# ======================================================================
# Project:    Closed borders, closed minds? COVID-related border closures,
#             EU support and hostility towards immigrants
#
# Script:     Data Preperation SOEP
#
# Authors:    Lisa Herbig (l.j.herbig@uva.nl)
#             Asli Unan (a.unan@uva.nl)
#
# Date:       2025-03-24
# ======================================================================

# Description: This script prepares the data for the analysis (based on SOEP-Core v37)

# ----------------------------------------------------------------------
# Load libraries
# ----------------------------------------------------------------------

library("foreign")
library("haven")
library("tidyverse")
library("plyr")
library("dplyr")
library("tidyr")

### LOCAL VARIABLES ###
# HOME
#path_in <- "~/Dropbox/Datasets/soep data/R_EN/raw"
#path_out <- "~/Dropbox/Datasets/soep data/R_EN/raw/out"

# SOEP
path_in <- "~/work/November_2024/R_in"
path_out <- "~/work/November_2024/R_out"

### READ DATA ###
bjp_raw <- readRDS(file.path(path_in, "bjp.rds"))
bjp= bjp_raw %>%
  dplyr::select(pid, hid,
                bjpmonin, bjptagin, bjpbirthy, sex, bjptransl9, bjpinta, bjp_04, bjp_12_01, bjp_29,bjp_51_01, bjp_65_q154, bjp_154_01, bjp_154_02, bjp_167, bjp_169_01, bjp_168, bjp_170, 
                bjp_174_01, bjp_174_02, bjp_174_03, bjp_174_04, bjp_174_05, bjp_174_06, bjp_174_07, bjp_174_08, bjp_174_09,bjp_174_11, 
                bjp_174_12, bjp_174_13, bjp_176, bjp_177_01, bjp_178_02, bjp_179, bjp_208_17, bjp_208_18, bjp_208_19,bjp_208_20, 
                bjp_326_q154, bjp_422_q155, bjp_423_q155, bjp_188)

bjp <- dplyr::rename(bjp,
                     hid_bjp=hid,
                     bjpsex=sex)

bip_raw <- readRDS(file.path(path_in, "bip.rds"))
bip= bip_raw %>%
  dplyr::select(pid, hid,
                bipmonin, biptagin, bipbirthy, sex, biptransl9, bipinta, bip_15, bip_22_02, bip_43, bip_67_01, bip_137_02, bip_137_03, bip_149_01, bip_149_02, bip_170_01, bip_170_02, bip_170_03, 
                bip_170_04, bip_170_05, bip_170_06,bip_170_07, bip_170_08, bip_170_09, bip_170_11, bip_170_12, bip_170_13,bip_171, bip_172, 
                bip_173_01, bip_174, bip_176_01, bip_176_02, bip_176_03, bip_176_04,bip_176_05, bip_177_01, bip_177_02, bip_177_03,
                bip_177_04, bip_177_05, bip_177_06, bip_178,bip_179_01, bip_181, bip_180, bip_182_02, bip_184, bip_186, bip_187, bip_188,
                bip_190, bip_200_17,bip_200_18, bip_200_19, bip_200_20, bip_175)

bip <- dplyr::rename(bip,
                     hid_bip=hid,
                     bipsex=sex)

bhp_raw <- readRDS(file.path(path_in, "bhp.rds"))
bhp= bhp_raw %>%
  dplyr::select(pid, hid,
                bhpmonin, bhptagin, bhpbirthy, sex, bhptransl9, bhpinta, bhp_186_01,bhp_186_02,bhp_186_03,bhp_186_04,bhp_186_05,bhp_186_06,bhp_186_07,bhp_186_08,bhp_186_09,bhp_186_11, 
                bhp_186_12, bhp_186_13, bhp_199,bhp_183, bhp_33,bhp_196,bhp_198,bhp_200_02,bhp_197_01, bhp_05,bhp_184_01, bhp_185,bhp_182, 
                bhp_423_q56, bhp_424_q56, bhp_167_01,bhp_167_02, bhp_157_17, bhp_157_18, bhp_157_19, bhp_157_20)

bhp <- dplyr::rename(bhp,
                     hid_bhp=hid,
                     bhpsex=sex)

bgp_raw <- readRDS(file.path(path_in, "bgp.rds"))
bgp= bgp_raw %>%
  dplyr::select(pid, hid,
                bgpmonin, bgptagin, bgpbirthy, bgpsex, bgpm_m_pbuh9, bgpinta, bgp14801,bgp14802,bgp14803, bgp14804,bgp14805,bgp14806,bgp14807,bgp14808,bgp14809,bgp14810,bgp14811,bgp14812,bgp161,
                bgp144,bgp31,bgp168,bgp158,bgp160,bgp16202,bgp15901,bgp05,bgp14501,bgp146,bgp143,bgp0112,bgpr331,bgpr337,bgp10802,bgp10803,
                bgp12201,bgp12202,bgp16917,bgp16918,bgp16919,bgp16920)

bgp <- dplyr::rename(bgp,
                     hid_bgp=hid)

bfp_raw <- readRDS(file.path(path_in, "bfp.rds"))
bfp= bfp_raw %>%
  dplyr::select(pid, hid,
                bfpmonin, bfptagin, bfpbirthy, bfpsex, bfpm_pbuh9, bfpinta, bfp14601, bfp14602,bfp14603,bfp14604,bfp14605,bfp14606,bfp14607,bfp14608,bfp14609,bfp14610,bfp14611,bfp14612,bfp161,
                bfp144,bfp32,bfp158,bfp160,bfp16202,bfp15901,bfp04,bfp14501,bfp14502,bfp143,bfp0504,bfp0510,bfp13301,bfp13302,bfp17217,
                bfp17218,bfp17219,bfp17220, bfp5801)

bfp <- dplyr::rename(bfp,
                     hid_bfp=hid)

bep_raw <- readRDS(file.path(path_in, "bep.rds"))
bep= bep_raw %>%
  dplyr::select(pid, hid,
                bepmonin, beptagin, bep12603, bep12601,bepm_pbuh9, bepinta, bep12301,bep12302,bep12304,bep12305,bep12306,bep12307,bep12308,bep12309,bep12310,bep12311,bep132,
                bep119,bep12,bep139,bep129,bep131,bep133,bep13001,bep04,bep12001,bep12002,bep118,bep9202,bep9203,bep9901,bep9902,bep14917,
                bep14918,bep14919,bep14920, bep143) 

bep <- dplyr::rename(bep,
                     hid_bep=hid,
                     bepsex=bep12601,
                     bepbirthy=bep12603)

bdp_raw <- readRDS(file.path(path_in, "bdp.rds"))
bdp= bdp_raw %>%
  dplyr::select(pid, hid,
                bdpmonin, bdptagin, bdp13403, bdp13401,bdpm_pbuh9, bdpinta, bdp13301,bdp13302,bdp13305,bdp13306,bdp13307,bdp13308,bdp13310,bdp13311,bdp13312,bdp13314,bdp146,bdp131,
                bdp18,bdpm_p_19001,bdp143,bdp145,bdp147,bdp14401,bdp154,bdp13201,bdp13202,bdp130,bdp11601,bdp11602,bdp15617,bdp15618,
                bdp15619,bdp15620)

bdp <- dplyr::rename(bdp,
                     hid_bdp=hid,
                     bdpsex=bdp13401,
                     bdpbirthy=bdp13403)

bcp_raw <- readRDS(file.path(path_in, "bcp.rds"))
bcp= bcp_raw %>%
  dplyr::select(pid, hid,
                bcpmonin, bcptagin, bcp12803, bcp12801, bcp12701, bcpinta, bcp12702,bcp12704,bcp12705,bcp12706,bcp12707,bcp12709,bcp12710,bcp12711,bcp12713,bcp142,bcp125,
                bcp11,bcp147,bcp139,bcp141,bcp143,bcp14001,bcp148,bcp12601,bcp12602,bcp124,bcp9402,bcp9403,bcp10501,bcp10502,bcp14917,
                bcp14918,bcp14919,bcp14920)

bcp <- dplyr::rename(bcp,
                     hid_bcp=hid,
                     bcpsex=bcp12801,
                     bcpbirthy=bcp12803)

bbp_raw <- readRDS(file.path(path_in, "bbp.rds"))
bbp= bbp_raw %>%
  dplyr::select(pid, hid,
                bbpmonin, bbptagin, bbp13202, bbp13201, bbp13101, bbpinta, bbp13102, bbp13104,bbp13105,bbp13106,bbp13107,bbp13109,bbp13110,bbp13111,bbp13113,bbp129,bbp09,
                bbp140,bbp14201,bbp143,bbp14101,bbp121,bbp13001,bbp13002,bbp128,bbp10201,bbp10202,bbp14817,bbp14818,bbp14819,bbp14820)

bbp <- dplyr::rename(bbp,
                     hid_bbp=hid,
                     bbpsex=bbp13201,
                     bbpbirthy=bbp13202)

bap_raw <- readRDS(file.path(path_in, "bap.rds"))
bap= bap_raw %>%
  dplyr::select(pid, hid,
                bapmonin, baptagin, bap15002, bap15001,bap13001, bapinta, bap13002,bap13004,bap13005,bap13006,bap13007,bap13009,bap13010,bap13011,bap13012,bap128,bap09,
                bap135,bap137,bap138,bap13601,bap123,bap12901,bap12902,bap127,bap0204,bap0210,bap9002,bap9003,bap9901,bap9902,bap15517,
                bap15518,bap15519,bap15520,bap142)

bap <- dplyr::rename(bap,
                     hid_bap=hid,
                     bapsex=bap15001,
                     bapbirthy=bap15002)

zp_raw <- readRDS(file.path(path_in, "zp.rds"))
zp= zp_raw %>%
  dplyr::select(pid, hid, 
                zpmonin, zptagin,zp12902, zp12901,zp12801, zpinta, zp12802,zp12804,zp12805,zp12806,zp12807,zp12809,zp12810,zp12811,zp12812,zp123,zp09,zp137,zp139,
                zp140,zp13801,zp121,zp12401,zp12402,zp122,zp9701,zp9702,zp15417,zp15418,zp15419,zp15420)

zp <- dplyr::rename(zp,
                    hid_zp=hid,
                    zpsex=zp12901,
                    zpbirthy=zp12902)

yp_raw <- readRDS(file.path(path_in, "yp.rds"))
yp= yp_raw %>%
  dplyr::select(pid, hid,
                ypmonin, yptagin, yp14802, yp14801,yp13201, ypinta, yp13202,yp13203,yp13204,yp13205,yp13207,yp13209,yp13210,yp13211,yp130,yp19 ,yp137,yp139,yp140,
                yp13801,yp10,yp13101,yp13102,yp129 ,yp10202,yp10203,yp11001,yp11002,yp153051,yp153052,yp153053,yp153054)

yp <- dplyr::rename(yp,
                    hid_yp=hid,
                    ypsex=yp14801,
                    ypbirthy=yp14802)

xp_raw <- readRDS(file.path(path_in, "xp.rds"))
xp= xp_raw %>%
  dplyr::select(pid, hid,
                xpmonin, xptagin, xp13102, xp13101, xp13001,xp13002,xpinta, xp13003,xp13004,xp13005,xp13006,xp13008,xp13009,xp13010,xp128,xp13,xp139,xp141,xp142,
                xp14001,xp12901,xp12902,xp127,xp10001,xp10002,xp14717,xp14718,xp14719,xp14720)

xp <- dplyr::rename(xp,
                    hid_xp=hid,
                    xpsex=xp13101,
                    xpbirthy=xp13102)

wp_raw <- readRDS(file.path(path_in, "wp.rds"))
wp= wp_raw %>%
  dplyr::select(pid, hid,
                wpmonin, wptagin, wp12402, wp12401,wp12101,wp12102, wpinta, wp12103,wp12104,wp12105,wp12106,wp12108,wp12109,wp12110,wp119,wp07,wp127,wp129,wp130,
                wp12801,wp123,wp12001,wp12002,wp118,wp9002,wp9003,wp9701,wp9702,wp14017,wp14018,wp14019,wp14020)

wp <- dplyr::rename(wp,
                    hid_wp=hid,
                    wpsex=wp12401,
                    wpbirthy=wp12402)

master_raw <- readRDS(file.path(path_in, "bkp.rds"))
table(master_raw$bkpyrin)
master_raw = master_raw %>%
  filter(bkpyrin < 2021)

master= master_raw %>%
  dplyr::select(pid, hid,
                
                bkpmonin, bkptagin, bkpbirthy, sex, bkptransl9, bkpinta, bkp_01_11, bkp_04, bkp_05_04, bkp_05_10, bkp_14_01,bkp_34,  bkp_57_01, bkp_63_02, bkp_126_02,
                bkp_126_03, bkp_143_01, bkp_143_02, bkp_168_01, bkp_168_02, bkp_168_03, bkp_168_04,
                bkp_168_05, bkp_168_06, bkp_168_07, bkp_168_08, bkp_168_09, bkp_168_10,
                bkp_168_11, bkp_168_12, bkp_168_13, bkp_168_14, bkp_168_15, bkp_169,
                bkp_170, bkp_171_01, bkp_172, bkp_173_01, bkp_173_02, bkp_173_03, bkp_173_04,
                bkp_173_05, bkp_174_01, bkp_174_02, bkp_174_03, bkp_174_04, bkp_174_05, bkp_174_06, 
                bkp_175, bkp_176, bkp_178, bkp_179, bkp_180_02, bkp_181, bkp_182_02, bkp_183, 
                bkp_184, bkp_185, bkp_186, bkp_187, bkp_188, bkp_198_17, bkp_198_18, bkp_198_19, bkp_198_20, bkp_67
  )

master <- dplyr::rename(master,
                        hid_bkp=hid,
                        bkpsex=sex)

### MERGE ###
master <- merge(master, bjp, by = "pid", all.x=T, all.y=F) #2020
master <- merge(master, bip, by = "pid", all.x=T, all.y=F) 
master <- merge(master, bhp, by = "pid", all.x=T, all.y=F)
master <- merge(master, bgp, by = "pid", all.x=T, all.y=F)
master <- merge(master, bfp, by = "pid", all.x=T, all.y=F)
master <- merge(master, bep, by = "pid", all.x=T, all.y=F)
master <- merge(master, bdp, by = "pid", all.x=T, all.y=F)
master <- merge(master, bcp, by = "pid", all.x=T, all.y=F)
master <- merge(master, bbp, by = "pid", all.x=T, all.y=F)
master <- merge(master, bap, by = "pid", all.x=T, all.y=F)
master <- merge(master, zp, by = "pid", all.x=T, all.y=F)
master <- merge(master, yp, by = "pid", all.x=T, all.y=F)
master <- merge(master, xp, by = "pid", all.x=T, all.y=F)
master <- merge(master, wp, by = "pid", all.x=T, all.y=F)  #2006

saveRDS(master, "master_merge.rds")

#########################################MASTER DATA IMPORT#######################################################
#########################################MASTER DATA IMPORT#######################################################
master_workor <- master
#######################################################CHANGE COLUMN NUMBERS##########################################
#######################################################CHANGE COLUMNS NUMBERS##########################################
master_year_20 <- master_workor %>%
  dplyr::select(1:68, 110, 167)
master_year_20$year = 2020
master_year_20$date = make_date(year=master_year_20$year, month=master_year_20$bkpmonin, day=master_year_20$bkptagin )
master_year_20 <- master_year_20 %>%
  select(-bkpmonin, -bkptagin)

master_year_19 <- master_workor %>%
  dplyr::select(1, 69:110)
master_year_19$year = 2019
master_year_19$date = make_date(year=master_year_19$year, month=master_year_19$bjpmonin, day=master_year_19$bjptagin )
master_year_19 <- master_year_19 %>%
  select(-bjpmonin, -bjptagin)

master_year_18 <- master_workor %>%
  dplyr::select(1,111:167)
master_year_18$year = 2018
master_year_18$date = make_date(year=master_year_18$year, month=master_year_18$bipmonin, day=master_year_18$biptagin )
master_year_18 <- master_year_18 %>%
  select(-bipmonin, -biptagin)

master_year_17 <- master_workor %>%
  dplyr::select(1,168:205)
master_year_17$year = 2017
master_year_17$date = make_date(year=master_year_17$year, month=master_year_17$bhpmonin, day=master_year_17$bhptagin )
master_year_17 <- master_year_17 %>%
  select(-bhpmonin, -bhptagin)

master_year_16 <- master_workor %>%
  dplyr::select(1,206:247)
master_year_16$year = 2016
master_year_16$date = make_date(year=master_year_16$year, month=master_year_16$bgpmonin, day=master_year_16$bgptagin )
master_year_16 <- master_year_16 %>%
  select(-bgpmonin, -bgptagin)

master_year_15 <- master_workor %>%
  dplyr::select(1,248:286)
master_year_15$year = 2015
master_year_15$date = make_date(year=master_year_15$year, month=master_year_15$bfpmonin, day=master_year_15$bfptagin )
master_year_15 <- master_year_15 %>%
  select(-bfpmonin, -bfptagin)

master_year_14 <- master_workor %>%
  dplyr::select(1,287:324)
master_year_14$year = 2014
master_year_14$date = make_date(year=master_year_14$year, month=master_year_14$bepmonin, day=master_year_14$beptagin )
master_year_14 <- master_year_14 %>%
  select(-bepmonin, -beptagin)

master_year_13 <- master_workor %>%
  dplyr::select(1,325:359)
master_year_13$year = 2013
master_year_13$date = make_date(year=master_year_13$year, month=master_year_13$bdpmonin, day=master_year_13$bdptagin )
master_year_13 <- master_year_13 %>%
  select(-bdpmonin, -bdptagin)

master_year_12 <- master_workor %>%
  dplyr::select(1,360:395)
master_year_12$year = 2012
master_year_12$date = make_date(year=master_year_12$year, month=master_year_12$bcpmonin, day=master_year_12$bcptagin )
master_year_12 <- master_year_12 %>%
  select(-bcpmonin, -bcptagin)

master_year_11 <- master_workor %>%
  dplyr::select(1,396:427)
master_year_11$year = 2011
master_year_11$date = make_date(year=master_year_11$year, month=master_year_11$bbpmonin, day=master_year_11$bbptagin )
master_year_11 <- master_year_11 %>%
  select(-bbpmonin, -bbptagin)

master_year_10 <- master_workor %>%
  dplyr::select(1,428:464)
master_year_10$year = 2010
master_year_10$date = make_date(year=master_year_10$year, month=master_year_10$bapmonin, day=master_year_10$baptagin )
master_year_10 <- master_year_10 %>%
  select(-bapmonin, -baptagin)

master_year_09 <- master_workor %>%
  dplyr::select(1,465:496)
master_year_09$year = 2009
master_year_09$date = make_date(year=master_year_09$year, month=master_year_09$zpmonin, day=master_year_09$zptagin )
master_year_09 <- master_year_09 %>%
  select(-zpmonin, -zptagin)

master_year_08 <- master_workor %>%
  dplyr::select(1,497:529)
master_year_08$year = 2008
master_year_08$date = make_date(year=master_year_08$year, month=master_year_08$ypmonin, day=master_year_08$yptagin )
master_year_08 <- master_year_08 %>%
  select(-ypmonin, -yptagin)

master_year_07 <- master_workor %>%
  dplyr::select(1,530:559)
master_year_07$year = 2007
master_year_07$date = make_date(year=master_year_07$year, month=master_year_07$xpmonin, day=master_year_07$xptagin )
master_year_07 <- master_year_07 %>%
  select(-xpmonin, -xptagin)

master_year_06 <- master_workor %>%
  dplyr::select(1,560:592)
master_year_06$year = 2006
master_year_06$date = make_date(year=master_year_06$year, month=master_year_06$wpmonin, day=master_year_06$wptagin )
master_year_06 <- master_year_06 %>%
  select(-wpmonin, -wptagin)

#2020

master_year_20 <- dplyr::rename(master_year_20,
                                hid=hid_bkp,
                                res_stat=bkp_181,	
                                visit_coo=bkp_183,	
                                con_europe=bkp_188,	
                                worry_eco_dev=bkp_168_01,
                                worry_eco_sit=bkp_168_02,	
                                worry_ret_prov=bkp_168_03,
                                worry_health=bkp_168_04,
                                worry_env_pro=bkp_168_05,
                                worry_env_change=bkp_168_06,
                                worry_peace=bkp_168_07,	
                                worry_crime=bkp_168_08,	
                                worry_soc_coh=bkp_168_09,	
                                worry_migration=bkp_168_10,	
                                worry_mig_host=bkp_168_11,	
                                worry_tech=bkp_168_12,	
                                worry_quali=bkp_168_13,	
                                worry_wlb=bkp_168_14,	
                                worry_sec_work=bkp_168_15,	
                                parents_german=bkp_179,	
                                one_party=bkp_170,	
                                language_media=bkp_185,	
                                refugee_eco=bkp_173_01,	
                                refugee_culture=bkp_173_02,	
                                refugee_ger_living=bkp_173_03,	
                                refugee_risk_short=bkp_173_04,	
                                refugee_risk_long=bkp_173_05,	
                                refugee_donation_lasty=bkp_174_01,	
                                refugee_donation_future=bkp_174_02,	
                                refugee_work_lasty=bkp_174_03,	
                                refugee_work_future=bkp_174_04,	
                                refugee_demo_lasty=bkp_174_05,	
                                refugee_demo_future=bkp_174_06,	
                                visit_coo_dur=bkp_184,	
                                work_status=bkp_34,
                                con_germany=bkp_187,	
                                german_citizenship=bkp_175,	
                                code_coo=bkp_182_02,
                                germ_citiz_since_birth=bkp_178,	
                                code_citizenship=bkp_180_02,	
                                second_citizenship=bkp_176,	
                                risk_taking=bkp_04,	
                                con_coo=bkp_186,	
                                polparty=bkp_171_01,	
                                party_intensity=bkp_172,	
                                political_interest=bkp_169,
                                satisf_democracy=bkp_01_11, 
                                pol_soc_engagement=bkp_05_04,
                                control_life=bkp_05_10,
                                run_down=bkp_126_02,
                                well_balanced=bkp_126_03,
                                doctor_3month_n_visits=bkp_143_01,
                                no_doctor_3month=bkp_143_02,
                                payments_other=bkp_198_17,
                                amount_payment_other=bkp_198_18,
                                others_germany=bkp_198_19,
                                others_foreign=bkp_198_20,
                                current_education=bkp_14_01,
                                occupational_status=bkp_57_01,
                                work_abroad=bkp_63_02,
                                birthyear=bkpbirthy,
                                sex=bkpsex,
                                no_survey_translation=bkptransl9,
                                interview_completion=bkpinta,
                                pid=pid,
                                work_home=bkp_67,
                                voting_2018=bip_175,
                                contact_friends=bjp_188)

#2019
master_year_19$bjp_m1 = NA
master_year_19$bjp_m2 = NA
master_year_19$bjp_m3 = NA
master_year_19$bjp_m4 = NA
master_year_19$bjp_m5 = NA
master_year_19$bjp_m6 = NA
master_year_19$bjp_m7 = NA
master_year_19$bjp_m8 = NA
master_year_19$bjp_m9 = NA
master_year_19$bjp_m10 = NA
master_year_19$bjp_m11 = NA
master_year_19$bjp_m12 = NA
master_year_19$bjp_m13 = NA
master_year_19$bjp_m14 = NA
master_year_19$bjp_m15 = NA
master_year_19$bjp_m16 = NA
master_year_19$bjp_m17 = NA
master_year_19$bjp_m18 = NA
master_year_19$bjp_m19 = NA
master_year_19$bjp_m20 = NA
master_year_19$bjp_m21 = NA
master_year_19$bjp_m22 = NA
master_year_19$bjp_m23 = NA
master_year_19$bjp_m24 = NA
master_year_19$bjp_m25 = NA
master_year_19$bjp_m26 = NA
master_year_19$bjp_m27 = NA

master_year_19 <- dplyr::rename(master_year_19,
                                hid=hid_bjp,
                                res_stat=bjp_179,
                                visit_coo=bjp_m1,
                                con_europe=bjp_m2,
                                worry_eco_dev=bjp_174_01,
                                worry_eco_sit=bjp_174_02,
                                worry_ret_prov=bjp_174_03,
                                worry_health=bjp_174_04,
                                worry_env_pro=bjp_174_05,
                                worry_env_change=bjp_174_06,
                                worry_peace=bjp_174_07,
                                worry_crime=bjp_174_08,
                                worry_soc_coh=bjp_174_09,
                                worry_migration=bjp_174_11,
                                worry_mig_host=bjp_174_12,
                                worry_tech=bjp_m3,
                                worry_quali=bjp_m4,
                                worry_wlb=bjp_m5,
                                worry_sec_work=bjp_174_13,
                                parents_german=bjp_m6,
                                one_party=bjp_168,
                                language_media=bjp_65_q154,
                                refugee_eco=bjp_m7,
                                refugee_culture=bjp_m8,
                                refugee_ger_living=bjp_m9,
                                refugee_risk_short=bjp_m10,
                                refugee_risk_long=bjp_m11,
                                refugee_donation_lasty=bjp_m12,
                                refugee_donation_future=bjp_m13,
                                refugee_work_lasty=bjp_m14,
                                refugee_work_future=bjp_m15,
                                refugee_demo_lasty=bjp_m16,
                                refugee_demo_future=bjp_m17,
                                visit_coo_dur=bjp_m18,
                                work_status=bjp_29,
                                con_germany=bjp_m19,
                                german_citizenship=bjp_176,
                                code_coo=bjp_m20,
                                germ_citiz_since_birth=bjp_326_q154,
                                code_citizenship=bjp_178_02,
                                second_citizenship=bjp_177_01,
                                risk_taking=bjp_04,
                                con_coo=bjp_m21,
                                polparty=bjp_169_01,
                                party_intensity=bjp_170,
                                political_interest=bjp_167,
                                satisf_democracy=bjp_m22, 
                                pol_soc_engagement=bjp_m23,
                                control_life=bjp_m24,
                                run_down=bjp_422_q155,
                                well_balanced=bjp_423_q155,
                                doctor_3month_n_visits=bjp_154_01,
                                no_doctor_3month=bjp_154_02,
                                payments_other=bjp_208_17,
                                amount_payment_other=bjp_208_18,
                                others_germany=bjp_208_19,
                                others_foreign=bjp_208_20,
                                current_education=bjp_12_01,
                                occupational_status=bjp_51_01,
                                work_abroad=bjp_m25,
                                birthyear=bjpbirthy,
                                sex=bjpsex,
                                no_survey_translation=bjptransl9,
                                interview_completion=bjpinta,
                                pid=pid,
                                work_home=bjp_m26,
                                voting_2018=bjp_m27,
                                contact_friends=bjp_188
)

#2018
master_year_18$bip_m1 = NA
master_year_18$bip_m2 = NA
master_year_18$bip_m3 = NA
master_year_18$bip_m4 = NA
master_year_18$bip_m5 = NA
master_year_18$bip_m6 = NA
master_year_18$bip_m7 = NA
master_year_18$bip_m8 = NA
master_year_18$bip_m9 = NA
master_year_18$bip_m10 = NA
master_year_18$bip_m11 = NA
master_year_18$bip_m12 = NA

master_year_18 <- dplyr::rename(master_year_18, 
                                hid=hid_bip,
                                res_stat=bip_184,
                                visit_coo=bip_186,
                                con_europe=bip_m1,
                                worry_eco_dev=bip_170_01,
                                worry_eco_sit=bip_170_02,
                                worry_ret_prov=bip_170_03,
                                worry_health=bip_170_04,
                                worry_env_pro=bip_170_05,
                                worry_env_change=bip_170_06,
                                worry_peace=bip_170_07,
                                worry_crime=bip_170_08,
                                worry_soc_coh=bip_170_09,
                                worry_migration=bip_170_11,
                                worry_mig_host=bip_170_12,
                                worry_tech=bip_m2,
                                worry_quali=bip_m3,
                                worry_wlb=bip_m4,
                                worry_sec_work=bip_170_13,
                                parents_german=bip_181,
                                one_party=bip_172,
                                language_media=bip_188,
                                refugee_eco=bip_176_01,
                                refugee_culture=bip_176_02,
                                refugee_ger_living=bip_176_03,
                                refugee_risk_short=bip_176_04,
                                refugee_risk_long=bip_176_05,
                                refugee_donation_lasty=bip_177_01,
                                refugee_donation_future=bip_177_02,
                                refugee_work_lasty=bip_177_03,
                                refugee_work_future=bip_177_04,
                                refugee_demo_lasty=bip_177_05,
                                refugee_demo_future=bip_177_06,
                                visit_coo_dur=bip_187,
                                work_status=bip_43,
                                con_germany=bip_190,
                                german_citizenship=bip_178,                       
                                code_coo=bip_m5,
                                germ_citiz_since_birth=bip_180,
                                code_citizenship=bip_182_02,
                                second_citizenship=bip_179_01,
                                risk_taking=bip_15,
                                con_coo=bip_m6,
                                polparty=bip_173_01,
                                party_intensity=bip_174,
                                political_interest=bip_171,
                                satisf_democracy=bip_m7, 
                                pol_soc_engagement=bip_m8,
                                control_life=bip_m9,
                                run_down=bip_137_02,
                                well_balanced=bip_137_03,
                                doctor_3month_n_visits=bip_149_01,
                                no_doctor_3month=bip_149_02,
                                payments_other=bip_200_17,
                                amount_payment_other=bip_200_18,
                                others_germany=bip_200_19,
                                others_foreign=bip_200_20,
                                current_education=bip_22_02,
                                occupational_status=bip_67_01,
                                work_abroad=bip_m10,
                                birthyear=bipbirthy,
                                sex=bipsex,
                                no_survey_translation=biptransl9,
                                interview_completion=bipinta,
                                pid=pid,
                                work_home=bip_m11,
                                voting_2018=bip_175,
                                contact_friends=bip_m12
)

#2017
master_year_17$bhp_m1 = NA
master_year_17$bhp_m2 = NA
master_year_17$bhp_m3 = NA
master_year_17$bhp_m4 = NA
master_year_17$bhp_m5 = NA
master_year_17$bhp_m6 = NA
master_year_17$bhp_m7 = NA
master_year_17$bhp_m8 = NA
master_year_17$bhp_m9 = NA
master_year_17$bhp_m10 = NA
master_year_17$bhp_m11 = NA
master_year_17$bhp_m12 = NA
master_year_17$bhp_m13 = NA
master_year_17$bhp_m14 = NA
master_year_17$bhp_m15 = NA
master_year_17$bhp_m16 = NA
master_year_17$bhp_m17 = NA
master_year_17$bhp_m18 = NA
master_year_17$bhp_m19 = NA
master_year_17$bhp_m20 = NA
master_year_17$bhp_m21 = NA
master_year_17$bhp_m22 = NA
master_year_17$bhp_m23 = NA
master_year_17$bhp_m24 = NA
master_year_17$bhp_m25 = NA
master_year_17$bhp_m26 = NA
master_year_17$bhp_m27 = NA
master_year_17$bhp_m28 = NA
master_year_17$bhp_m29 = NA
master_year_17$bhp_m30 = NA
master_year_17$bhp_m31 = NA

master_year_17 <- dplyr::rename(master_year_17, 
                                hid=hid_bhp,
                                res_stat=bhp_m1,
                                visit_coo=bhp_m2,
                                con_europe=bhp_m3,
                                worry_eco_dev=bhp_186_01,
                                worry_eco_sit=bhp_186_02,
                                worry_ret_prov=bhp_186_03,
                                worry_health=bhp_186_04,
                                worry_env_pro=bhp_186_05,
                                worry_env_change=bhp_186_06,
                                worry_peace=bhp_186_07,
                                worry_crime=bhp_186_08,
                                worry_soc_coh=bhp_186_09,
                                worry_migration=bhp_186_11,
                                worry_mig_host=bhp_186_12,
                                worry_tech=bhp_m4,
                                worry_quali=bhp_m5,
                                worry_wlb=bhp_m6,
                                worry_sec_work=bhp_186_13,
                                parents_german=bhp_199,
                                one_party=bhp_183,
                                language_media=bhp_m7,
                                refugee_eco=bhp_m8,
                                refugee_culture=bhp_m9,
                                refugee_ger_living=bhp_m10,
                                refugee_risk_short=bhp_m11,
                                refugee_risk_long=bhp_m12,
                                refugee_donation_lasty=bhp_m13,
                                refugee_donation_future=bhp_m14,
                                refugee_work_lasty=bhp_m15,
                                refugee_work_future=bhp_m16,
                                refugee_demo_lasty=bhp_m17,
                                refugee_demo_future=bhp_m18,
                                visit_coo_dur=bhp_m19,
                                work_status=bhp_33,
                                con_germany=bhp_m20,
                                german_citizenship=bhp_196,                       
                                code_coo=bhp_m21,
                                germ_citiz_since_birth=bhp_198,
                                code_citizenship=bhp_200_02,
                                second_citizenship=bhp_197_01,
                                risk_taking=bhp_05,
                                con_coo=bhp_m22,
                                polparty=bhp_184_01,
                                party_intensity=bhp_185,
                                political_interest=bhp_182,
                                satisf_democracy=bhp_m23, 
                                pol_soc_engagement=bhp_m24,
                                control_life=bhp_m25,
                                run_down=bhp_423_q56,
                                well_balanced=bhp_424_q56,
                                doctor_3month_n_visits=bhp_167_01,
                                no_doctor_3month=bhp_167_02,
                                payments_other=bhp_157_17,
                                amount_payment_other=bhp_157_18,
                                others_germany=bhp_157_19,
                                others_foreign=bhp_157_20,
                                current_education=bhp_m26,
                                occupational_status=bhp_m27,
                                work_abroad=bhp_m28,
                                birthyear=bhpbirthy,
                                sex=bhpsex,
                                no_survey_translation=bhptransl9,
                                interview_completion=bhpinta,
                                pid=pid,
                                work_home=bhp_m29,
                                voting_2018=bhp_m30,
                                contact_friends=bhp_m31
)

#2016
master_year_16$bgp_m1 = NA
master_year_16$bgp_m2 = NA
master_year_16$bgp_m3 = NA
master_year_16$bgp_m4 = NA
master_year_16$bgp_m5 = NA
master_year_16$bgp_m6 = NA
master_year_16$bgp_m7 = NA
master_year_16$bgp_m8 = NA
master_year_16$bgp_m9 = NA
master_year_16$bgp_m10 = NA
master_year_16$bgp_m11 = NA
master_year_16$bgp_m12 = NA
master_year_16$bgp_m13 = NA
master_year_16$bgp_m14 = NA
master_year_16$bgp_m15 = NA
master_year_16$bgp_m16 = NA
master_year_16$bgp_m17 = NA
master_year_16$bgp_m18 = NA
master_year_16$bgp_m19 = NA
master_year_16$bgp_m20 = NA
master_year_16$bgp_m21 = NA
master_year_16$bgp_m22 = NA
master_year_16$bgp_m23 = NA
master_year_16$bgp_m24 = NA
master_year_16$bgp_m25 = NA
master_year_16$bgp_m26 = NA
master_year_16$bgp_m27 = NA

master_year_16 <- dplyr::rename(master_year_16, 
                                hid=hid_bgp,
                                res_stat=bgp_m1,
                                visit_coo=bgp_m2,
                                con_europe=bgp_m3,
                                worry_eco_dev=bgp14801,
                                worry_eco_sit=bgp14802,
                                worry_ret_prov=bgp14803,
                                worry_health=bgp14804,
                                worry_env_pro=bgp14805,
                                worry_env_change=bgp14806,
                                worry_peace=bgp14807,
                                worry_crime=bgp14808,
                                worry_soc_coh=bgp14809,
                                worry_migration=bgp14810,
                                worry_mig_host=bgp14811,
                                worry_tech=bgp_m4,
                                worry_quali=bgp_m5,
                                worry_wlb=bgp_m6,
                                worry_sec_work=bgp14812,
                                parents_german=bgp161,
                                one_party=bgp144,
                                language_media=bgp_m7,
                                refugee_eco=bgp_m8,
                                refugee_culture=bgp_m9,
                                refugee_ger_living=bgp_m10,
                                refugee_risk_short=bgp_m11,
                                refugee_risk_long=bgp_m12,
                                refugee_donation_lasty=bgp_m13,
                                refugee_donation_future=bgp_m14,
                                refugee_work_lasty=bgp_m15,
                                refugee_work_future=bgp_m16,
                                refugee_demo_lasty=bgp_m17,
                                refugee_demo_future=bgp_m18,
                                visit_coo_dur=bgp_m19,
                                work_status=bgp31,
                                con_germany=bgp168,
                                german_citizenship=bgp158,                       
                                code_coo=bgp_m20,
                                germ_citiz_since_birth=bgp160,
                                code_citizenship=bgp16202,
                                second_citizenship=bgp15901,
                                risk_taking=bgp05,
                                con_coo=bgp_m21,
                                polparty=bgp14501,
                                party_intensity=bgp146,
                                political_interest=bgp143,
                                satisf_democracy=bgp0112, 
                                pol_soc_engagement=bgpr331,
                                control_life=bgpr337,
                                run_down=bgp10802,
                                well_balanced=bgp10803,
                                doctor_3month_n_visits=bgp12201,
                                no_doctor_3month=bgp12202,
                                payments_other=bgp16917,
                                amount_payment_other=bgp16918,
                                others_germany=bgp16919,
                                others_foreign=bgp16920,
                                current_education=bgp_m22,
                                occupational_status=bgp_m23,
                                work_abroad=bgp_m24,
                                birthyear=bgpbirthy,
                                sex=bgpsex,
                                no_survey_translation=bgpm_m_pbuh9,
                                interview_completion=bgpinta,
                                pid=pid,
                                work_home=bgp_m25,
                                voting_2018=bgp_m26,
                                contact_friends=bgp_m27
)

#2015
master_year_15$bfp_m1 = NA
master_year_15$bfp_m2 = NA
master_year_15$bfp_m3 = NA
master_year_15$bfp_m4 = NA
master_year_15$bfp_m5 = NA
master_year_15$bfp_m6 = NA
master_year_15$bfp_m7 = NA
master_year_15$bfp_m8 = NA
master_year_15$bfp_m9 = NA
master_year_15$bfp_m10 = NA
master_year_15$bfp_m11 = NA
master_year_15$bfp_m12 = NA
master_year_15$bfp_m13 = NA
master_year_15$bfp_m14 = NA
master_year_15$bfp_m15 = NA
master_year_15$bfp_m16 = NA
master_year_15$bfp_m17 = NA
master_year_15$bfp_m18 = NA
master_year_15$bfp_m19 = NA
master_year_15$bfp_m20 = NA
master_year_15$bfp_m21 = NA
master_year_15$bfp_m22 = NA
master_year_15$bfp_m23 = NA
master_year_15$bfp_m24 = NA
master_year_15$bfp_m25 = NA
master_year_15$bfp_m26 = NA
master_year_15$bfp_m27 = NA
master_year_15$bfp_m28 = NA
master_year_15$bfp_m29 = NA
master_year_15$bfp_m30 = NA

master_year_15 <- dplyr::rename(master_year_15, 
                                hid=hid_bfp,
                                res_stat=bfp_m1,
                                visit_coo=bfp_m2,
                                con_europe=bfp_m3,
                                worry_eco_dev=bfp14601,
                                worry_eco_sit=bfp14602,
                                worry_ret_prov=bfp14603,
                                worry_health=bfp14604,
                                worry_env_pro=bfp14605,
                                worry_env_change=bfp14606,
                                worry_peace=bfp14607,
                                worry_crime=bfp14608,
                                worry_soc_coh=bfp14609,
                                worry_migration=bfp14610,
                                worry_mig_host=bfp14611,
                                worry_tech=bfp_m4,
                                worry_quali=bfp_m5,
                                worry_wlb=bfp_m6,
                                worry_sec_work=bfp14612,
                                parents_german=bfp161,
                                one_party=bfp144,
                                language_media=bfp_m7,
                                refugee_eco=bfp_m8,
                                refugee_culture=bfp_m9,
                                refugee_ger_living=bfp_m10,
                                refugee_risk_short=bfp_m11,
                                refugee_risk_long=bfp_m12,
                                refugee_donation_lasty=bfp_m13,
                                refugee_donation_future=bfp_m14,
                                refugee_work_lasty=bfp_m15,
                                refugee_work_future=bfp_m16,
                                refugee_demo_lasty=bfp_m17,
                                refugee_demo_future=bfp_m18,
                                visit_coo_dur=bfp_m19,
                                work_status=bfp32,
                                con_germany=bfp_m20,
                                german_citizenship=bfp158,                       
                                code_coo=bfp_m21,
                                germ_citiz_since_birth=bfp160,
                                code_citizenship=bfp16202,
                                second_citizenship=bfp15901,
                                risk_taking=bfp04,
                                con_coo=bfp_m22,
                                polparty=bfp14501,
                                party_intensity=bfp14502,
                                political_interest=bfp143,
                                satisf_democracy=bfp_m23, 
                                pol_soc_engagement=bfp0504,
                                control_life=bfp0510,
                                run_down=bfp_m24,
                                well_balanced=bfp_m25,
                                doctor_3month_n_visits=bfp13301,
                                no_doctor_3month=bfp13302,
                                payments_other=bfp17217,
                                amount_payment_other=bfp17218,
                                others_germany=bfp17219,
                                others_foreign=bfp17220,
                                current_education=bfp_m26,
                                occupational_status=bfp5801,
                                work_abroad=bfp_m27,
                                birthyear=bfpbirthy,
                                sex=bfpsex,
                                no_survey_translation=bfpm_pbuh9,
                                interview_completion=bfpinta,
                                pid=pid,
                                work_home=bfp_m28,
                                voting_2018=bfp_m29,
                                contact_friends=bfp_m30
)

#2014
master_year_14$bep_m1 = NA
master_year_14$bep_m2 = NA
master_year_14$bep_m3 = NA
master_year_14$bep_m4 = NA
master_year_14$bep_m5 = NA
master_year_14$bep_m6 = NA
master_year_14$bep_m7 = NA
master_year_14$bep_m8 = NA
master_year_14$bep_m9 = NA
master_year_14$bep_m10 = NA
master_year_14$bep_m11 = NA
master_year_14$bep_m12 = NA
master_year_14$bep_m13 = NA
master_year_14$bep_m14 = NA
master_year_14$bep_m15 = NA
master_year_14$bep_m16 = NA
master_year_14$bep_m17 = NA
master_year_14$bep_m18 = NA
master_year_14$bep_m19 = NA
master_year_14$bep_m20 = NA
master_year_14$bep_m21 = NA
master_year_14$bep_m22 = NA
master_year_14$bep_m23 = NA
master_year_14$bep_m24 = NA
master_year_14$bep_m25 = NA
master_year_14$bep_m26 = NA
master_year_14$bep_m27 = NA
master_year_14$bep_m28 = NA
master_year_14$bep_m29 = NA
master_year_14$bep_m30 = NA
master_year_14$bep_m31 = NA

master_year_14 <- dplyr::rename(master_year_14, 
                                hid=hid_bep,
                                res_stat=bep_m1,
                                visit_coo=bep_m2,
                                con_europe=bep_m3,
                                worry_eco_dev=bep12301,
                                worry_eco_sit=bep12302,
                                worry_ret_prov=bep_m4,
                                worry_health=bep12304,
                                worry_env_pro=bep12305,
                                worry_env_change=bep12306,
                                worry_peace=bep12307,
                                worry_crime=bep12308,
                                worry_soc_coh=bep_m5,
                                worry_migration=bep12309,
                                worry_mig_host=bep12310,
                                worry_tech=bep_m6,
                                worry_quali=bep_m7,
                                worry_wlb=bep_m8,
                                worry_sec_work=bep12311,
                                parents_german=bep132,
                                one_party=bep119,
                                language_media=bep_m9,
                                refugee_eco=bep_m10,
                                refugee_culture=bep_m11,
                                refugee_ger_living=bep_m12,
                                refugee_risk_short=bep_m13,
                                refugee_risk_long=bep_m14,
                                refugee_donation_lasty=bep_m15,
                                refugee_donation_future=bep_m16,
                                refugee_work_lasty=bep_m17,
                                refugee_work_future=bep_m18,
                                refugee_demo_lasty=bep_m19,
                                refugee_demo_future=bep_m20,
                                visit_coo_dur=bep_m21,
                                work_status=bep12,
                                con_germany=bep139,
                                german_citizenship=bep129,                       
                                code_coo=bep_m22,
                                germ_citiz_since_birth=bep131,
                                code_citizenship=bep133,
                                second_citizenship=bep13001,
                                risk_taking=bep04,
                                con_coo=bep_m23,
                                polparty=bep12001,
                                party_intensity=bep12002,
                                political_interest=bep118,
                                satisf_democracy=bep_m24, 
                                pol_soc_engagement=bep_m25,
                                control_life=bep_m26,
                                run_down=bep9202,
                                well_balanced=bep9203,
                                doctor_3month_n_visits=bep9901,
                                no_doctor_3month=bep9902,
                                payments_other=bep14917,
                                amount_payment_other=bep14918,
                                others_germany=bep14919,
                                others_foreign=bep14920,
                                current_education=bep_m27,
                                occupational_status=bep_m28,
                                work_abroad=bep_m29,
                                birthyear=bepbirthy,
                                sex=bepsex,
                                no_survey_translation=bepm_pbuh9,
                                interview_completion=bepinta,
                                pid=pid,
                                work_home=bep_m30,
                                voting_2018=bep_m31,
                                contact_friends=bep143
)



#2013
master_year_13$bdp_m1 = NA
master_year_13$bdp_m2 = NA
master_year_13$bdp_m3 = NA
master_year_13$bdp_m4 = NA
master_year_13$bdp_m5 = NA
master_year_13$bdp_m6 = NA
master_year_13$bdp_m7 = NA
master_year_13$bdp_m8 = NA
master_year_13$bdp_m9 = NA
master_year_13$bdp_m10 = NA
master_year_13$bdp_m11 = NA
master_year_13$bdp_m12 = NA
master_year_13$bdp_m13 = NA
master_year_13$bdp_m14 = NA
master_year_13$bdp_m15 = NA
master_year_13$bdp_m16 = NA
master_year_13$bdp_m17 = NA
master_year_13$bdp_m18 = NA
master_year_13$bdp_m19 = NA
master_year_13$bdp_m20 = NA
master_year_13$bdp_m21 = NA
master_year_13$bdp_m22 = NA
master_year_13$bdp_m23 = NA
master_year_13$bdp_m24 = NA
master_year_13$bdp_m25 = NA
master_year_13$bdp_m26 = NA
master_year_13$bdp_m27 = NA
master_year_13$bdp_m28 = NA
master_year_13$bdp_m29 = NA
master_year_13$bdp_m30 = NA
master_year_13$bdp_m31 = NA
master_year_13$bdp_m32 = NA
master_year_13$bdp_m33 = NA
master_year_13$bdp_m34 = NA

master_year_13 <- dplyr::rename(master_year_13, 
                                hid=hid_bdp,
                                res_stat=bdp_m1,
                                visit_coo=bdp_m2,
                                con_europe=bdp_m3,
                                worry_eco_dev=bdp13301,
                                worry_eco_sit=bdp13302,
                                worry_ret_prov=bdp_m4,
                                worry_health=bdp13305,
                                worry_env_pro=bdp13306,
                                worry_env_change=bdp13307,
                                worry_peace=bdp13308,
                                worry_crime=bdp13310,
                                worry_soc_coh=bdp_m5,
                                worry_migration=bdp13311,
                                worry_mig_host=bdp13312,
                                worry_tech=bdp_m6,
                                worry_quali=bdp_m7,
                                worry_wlb=bdp_m8,
                                worry_sec_work=bdp13314,
                                parents_german=bdp146,
                                one_party=bdp131,
                                language_media=bdp_m9,
                                refugee_eco=bdp_m10,
                                refugee_culture=bdp_m11,
                                refugee_ger_living=bdp_m12,
                                refugee_risk_short=bdp_m13,
                                refugee_risk_long=bdp_m14,
                                refugee_donation_lasty=bdp_m15,
                                refugee_donation_future=bdp_m16,
                                refugee_work_lasty=bdp_m17,
                                refugee_work_future=bdp_m18,
                                refugee_demo_lasty=bdp_m19,
                                refugee_demo_future=bdp_m20,
                                visit_coo_dur=bdp_m21,
                                work_status=bdp18,
                                con_germany=bdpm_p_19001,
                                german_citizenship=bdp143,                       
                                code_coo=bdp_m22,
                                germ_citiz_since_birth=bdp145,
                                code_citizenship=bdp147,
                                second_citizenship=bdp14401,
                                risk_taking=bdp154,
                                con_coo=bdp_m23,
                                polparty=bdp13201,
                                party_intensity=bdp13202,
                                political_interest=bdp130,
                                satisf_democracy=bdp_m24,
                                pol_soc_engagement=bdp_m25,
                                control_life=bdp_m26,
                                run_down=bdp_m27,
                                well_balanced=bdp_m28,
                                doctor_3month_n_visits=bdp11601,
                                no_doctor_3month=bdp11602,
                                payments_other=bdp15617,
                                amount_payment_other=bdp15618,
                                others_germany=bdp15619,
                                others_foreign=bdp15620,
                                current_education=bdp_m29,
                                occupational_status=bdp_m30,
                                work_abroad=bdp_m31,
                                birthyear=bdpbirthy,
                                sex=bdpsex,
                                no_survey_translation=bdpm_pbuh9,
                                interview_completion=bdpinta,
                                pid=pid,
                                work_home=bdp_m32,
                                voting_2018=bdp_m33,
                                contact_friends=bdp_m34
)

#2012
master_year_12$bcp_m1 = NA
master_year_12$bcp_m2 = NA
master_year_12$bcp_m3 = NA
master_year_12$bcp_m4 = NA
master_year_12$bcp_m5 = NA
master_year_12$bcp_m6 = NA
master_year_12$bcp_m7 = NA
master_year_12$bcp_m8 = NA
master_year_12$bcp_m9 = NA
master_year_12$bcp_m10 = NA
master_year_12$bcp_m11 = NA
master_year_12$bcp_m12 = NA
master_year_12$bcp_m13 = NA
master_year_12$bcp_m14 = NA
master_year_12$bcp_m15 = NA
master_year_12$bcp_m16 = NA
master_year_12$bcp_m17 = NA
master_year_12$bcp_m18 = NA
master_year_12$bcp_m19 = NA
master_year_12$bcp_m20 = NA
master_year_12$bcp_m21 = NA
master_year_12$bcp_m22 = NA
master_year_12$bcp_m23 = NA
master_year_12$bcp_m24 = NA
master_year_12$bcp_m25 = NA
master_year_12$bcp_m26 = NA
master_year_12$bcp_m27 = NA
master_year_12$bcp_m28 = NA
master_year_12$bcp_m29 = NA
master_year_12$bcp_m30 = NA
master_year_12$bcp_m31 = NA
master_year_12$bcp_m32 = NA
master_year_12$bcp_m33 = NA

master_year_12 <- dplyr::rename(master_year_12, 
                                hid=hid_bcp,
                                res_stat=bcp_m1,
                                visit_coo=bcp_m2,
                                con_europe=bcp_m3,
                                worry_eco_dev=bcp12701,
                                worry_eco_sit=bcp12702,
                                worry_ret_prov=bcp_m4,
                                worry_health=bcp12704,
                                worry_env_pro=bcp12705,
                                worry_env_change=bcp12706,
                                worry_peace=bcp12707,
                                worry_crime=bcp12709,
                                worry_soc_coh=bcp_m5,
                                worry_migration=bcp12710,
                                worry_mig_host=bcp12711,
                                worry_tech=bcp_m6,
                                worry_quali=bcp_m7,
                                worry_wlb=bcp_m8,
                                worry_sec_work=bcp12713,
                                parents_german=bcp142,
                                one_party=bcp125,
                                language_media=bcp_m9,
                                refugee_eco=bcp_m10,
                                refugee_culture=bcp_m11,
                                refugee_ger_living=bcp_m12,
                                refugee_risk_short=bcp_m13,
                                refugee_risk_long=bcp_m14,
                                refugee_donation_lasty=bcp_m15,
                                refugee_donation_future=bcp_m16,
                                refugee_work_lasty=bcp_m17,
                                refugee_work_future=bcp_m18,
                                refugee_demo_lasty=bcp_m19,
                                refugee_demo_future=bcp_m20,
                                visit_coo_dur=bcp_m21,
                                work_status=bcp11,
                                con_germany=bcp147,
                                german_citizenship=bcp139,
                                code_coo=bcp_m22,
                                germ_citiz_since_birth=bcp141,
                                code_citizenship=bcp143,
                                second_citizenship=bcp14001,
                                risk_taking=bcp148,
                                con_coo=bcp_m23,
                                polparty=bcp12601,
                                party_intensity=bcp12602,
                                political_interest=bcp124,
                                satisf_democracy=bcp_m24, 
                                pol_soc_engagement=bcp_m25,
                                control_life=bcp_m26,
                                run_down=bcp9402,
                                well_balanced=bcp9403,
                                doctor_3month_n_visits=bcp10501,
                                no_doctor_3month=bcp10502,
                                payments_other=bcp14917,
                                amount_payment_other=bcp14918,
                                others_germany=bcp14919,
                                others_foreign=bcp14920,
                                current_education=bcp_m27,
                                occupational_status=bcp_m28,
                                work_abroad=bcp_m29,
                                birthyear=bcpbirthy,
                                sex=bcpsex,
                                no_survey_translation=bcp_m30,
                                interview_completion=bcpinta,
                                pid=pid,
                                work_home=bcp_m31,
                                voting_2018=bcp_m32,
                                contact_friends=bcp_m33
)

#2011
master_year_11$bbp_m1 = NA
master_year_11$bbp_m2 = NA
master_year_11$bbp_m3 = NA
master_year_11$bbp_m4 = NA
master_year_11$bbp_m5 = NA
master_year_11$bbp_m6 = NA
master_year_11$bbp_m7 = NA
master_year_11$bbp_m8 = NA
master_year_11$bbp_m9 = NA
master_year_11$bbp_m10 = NA
master_year_11$bbp_m11 = NA
master_year_11$bbp_m12 = NA
master_year_11$bbp_m13 = NA
master_year_11$bbp_m14 = NA
master_year_11$bbp_m15 = NA
master_year_11$bbp_m16 = NA
master_year_11$bbp_m17 = NA
master_year_11$bbp_m18 = NA
master_year_11$bbp_m19 = NA
master_year_11$bbp_m20 = NA
master_year_11$bbp_m21 = NA
master_year_11$bbp_m22 = NA
master_year_11$bbp_m23 = NA
master_year_11$bbp_m24 = NA
master_year_11$bbp_m25 = NA
master_year_11$bbp_m26 = NA
master_year_11$bbp_m27 = NA
master_year_11$bbp_m28 = NA
master_year_11$bbp_m29 = NA
master_year_11$bbp_m30 = NA
master_year_11$bbp_m31 = NA
master_year_11$bbp_m32 = NA
master_year_11$bbp_m33 = NA
master_year_11$bbp_m34 = NA
master_year_11$bbp_m35 = NA
master_year_11$bbp_m36 = NA
master_year_11$bbp_m37 = NA

master_year_11 <- dplyr::rename(master_year_11, 
                                hid=hid_bbp,
                                res_stat=bbp_m1,
                                visit_coo=bbp_m2,
                                con_europe=bbp_m3,
                                worry_eco_dev=bbp13101,
                                worry_eco_sit=bbp13102,
                                worry_ret_prov=bbp_m4,
                                worry_health=bbp13104,
                                worry_env_pro=bbp13105,
                                worry_env_change=bbp13106,
                                worry_peace=bbp13107,
                                worry_crime=bbp13109,
                                worry_soc_coh=bbp_m5,
                                worry_migration=bbp13110,
                                worry_mig_host=bbp13111,
                                worry_tech=bbp_m6,
                                worry_quali=bbp_m7,
                                worry_wlb=bbp_m8,
                                worry_sec_work=bbp13113,
                                parents_german=bbp_m9,
                                one_party=bbp129,
                                language_media=bbp_m10,
                                refugee_eco=bbp_m11,
                                refugee_culture=bbp_m12,
                                refugee_ger_living=bbp_m13,
                                refugee_risk_short=bbp_m14,
                                refugee_risk_long=bbp_m15,
                                refugee_donation_lasty=bbp_m16,
                                refugee_donation_future=bbp_m17,
                                refugee_work_lasty=bbp_m18,
                                refugee_work_future=bbp_m19,
                                refugee_demo_lasty=bbp_m20,
                                refugee_demo_future=bbp_m21,
                                visit_coo_dur=bbp_m22,
                                work_status=bbp09,
                                con_germany=bbp_m23,
                                german_citizenship=bbp140,                       
                                code_coo=bbp_m24,
                                germ_citiz_since_birth=bbp14201,
                                code_citizenship=bbp143,
                                second_citizenship=bbp14101,
                                risk_taking=bbp121,
                                con_coo=bbp_m25,
                                polparty=bbp13001,
                                party_intensity=bbp13002,
                                political_interest=bbp128,
                                satisf_democracy=bbp_m26, 
                                pol_soc_engagement=bbp_m27,
                                control_life=bbp_m28,
                                run_down=bbp_m29,
                                well_balanced=bbp_m30,
                                doctor_3month_n_visits=bbp10201,
                                no_doctor_3month=bbp10202,
                                payments_other=bbp14817,
                                amount_payment_other=bbp14818,
                                others_germany=bbp14819,
                                others_foreign=bbp14820,
                                current_education=bbp_m31,
                                occupational_status=bbp_m32,
                                work_abroad=bbp_m33,
                                birthyear=bbpbirthy,
                                sex=bbpsex,
                                no_survey_translation=bbp_m34,
                                interview_completion=bbpinta,
                                pid=pid,
                                work_home=bbp_m35,
                                voting_2018=bbp_m36,
                                contact_friends=bbp_m37
)

#2010
master_year_10$bap_m1 = NA
master_year_10$bap_m2 = NA
master_year_10$bap_m3 = NA
master_year_10$bap_m4 = NA
master_year_10$bap_m5 = NA
master_year_10$bap_m6 = NA
master_year_10$bap_m7 = NA
master_year_10$bap_m8 = NA
master_year_10$bap_m9 = NA
master_year_10$bap_m10 = NA
master_year_10$bap_m11 = NA
master_year_10$bap_m12 = NA
master_year_10$bap_m13 = NA
master_year_10$bap_m14 = NA
master_year_10$bap_m15 = NA
master_year_10$bap_m16 = NA
master_year_10$bap_m17 = NA
master_year_10$bap_m18 = NA
master_year_10$bap_m19 = NA
master_year_10$bap_m20 = NA
master_year_10$bap_m21 = NA
master_year_10$bap_m22 = NA
master_year_10$bap_m23 = NA
master_year_10$bap_m24 = NA
master_year_10$bap_m25 = NA
master_year_10$bap_m26 = NA
master_year_10$bap_m27 = NA
master_year_10$bap_m28 = NA
master_year_10$bap_m29 = NA
master_year_10$bap_m30 = NA
master_year_10$bap_m31 = NA
master_year_10$bap_m32 = NA


master_year_10 <- dplyr::rename(master_year_10, 
                                hid=hid_bap,
                                res_stat=bap_m1,
                                visit_coo=bap_m2,
                                con_europe=bap_m3,
                                worry_eco_dev=bap13001,
                                worry_eco_sit=bap13002,
                                worry_ret_prov=bap_m4,
                                worry_health=bap13004,
                                worry_env_pro=bap13005,
                                worry_env_change=bap13006,
                                worry_peace=bap13007,
                                worry_crime=bap13009,
                                worry_soc_coh=bap_m5,
                                worry_migration=bap13010,
                                worry_mig_host=bap13011,
                                worry_tech=bap_m6,
                                worry_quali=bap_m7,
                                worry_wlb=bap_m8,
                                worry_sec_work=bap13012,
                                parents_german=bap_m9,
                                one_party=bap128,
                                language_media=bap_m10,
                                refugee_eco=bap_m11,
                                refugee_culture=bap_m12,
                                refugee_ger_living=bap_m13,
                                refugee_risk_short=bap_m14,
                                refugee_risk_long=bap_m15,
                                refugee_donation_lasty=bap_m16,
                                refugee_donation_future=bap_m17,
                                refugee_work_lasty=bap_m18,
                                refugee_work_future=bap_m19,
                                refugee_demo_lasty=bap_m20,
                                refugee_demo_future=bap_m21,
                                visit_coo_dur=bap_m22,
                                work_status=bap09,
                                con_germany=bap142,
                                german_citizenship=bap135,                       
                                code_coo=bap_m23,
                                germ_citiz_since_birth=bap137,
                                code_citizenship=bap138,
                                second_citizenship=bap13601,
                                risk_taking=bap123,
                                con_coo=bap_m24,
                                polparty=bap12901,
                                party_intensity=bap12902,
                                political_interest=bap127,
                                satisf_democracy=bap_m25, 
                                pol_soc_engagement=bap0204,
                                control_life=bap0210,
                                run_down=bap9002,
                                well_balanced=bap9003,
                                doctor_3month_n_visits=bap9901,
                                no_doctor_3month=bap9902,
                                payments_other=bap15517,
                                amount_payment_other=bap15518,
                                others_germany=bap15519,
                                others_foreign=bap15520,
                                current_education=bap_m26,
                                occupational_status=bap_m27,
                                work_abroad=bap_m28,
                                birthyear=bapbirthy,
                                sex=bapsex,
                                no_survey_translation=bap_m29,
                                interview_completion=bapinta,
                                pid=pid,
                                work_home=bap_m30,
                                voting_2018=bap_m31,
                                contact_friends=bap_m32
)

#2009
master_year_09$zp_m1 = NA
master_year_09$zp_m2 = NA
master_year_09$zp_m3 = NA
master_year_09$zp_m4 = NA
master_year_09$zp_m5 = NA
master_year_09$zp_m6 = NA
master_year_09$zp_m7 = NA
master_year_09$zp_m8 = NA
master_year_09$zp_m9 = NA
master_year_09$zp_m10 = NA
master_year_09$zp_m11 = NA
master_year_09$zp_m12 = NA
master_year_09$zp_m13 = NA
master_year_09$zp_m14 = NA
master_year_09$zp_m15 = NA
master_year_09$zp_m16 = NA
master_year_09$zp_m17 = NA
master_year_09$zp_m18 = NA
master_year_09$zp_m19 = NA
master_year_09$zp_m20 = NA
master_year_09$zp_m21 = NA
master_year_09$zp_m22 = NA
master_year_09$zp_m23 = NA
master_year_09$zp_m24 = NA
master_year_09$zp_m25 = NA
master_year_09$zp_m26 = NA
master_year_09$zp_m27 = NA
master_year_09$zp_m28 = NA
master_year_09$zp_m29 = NA
master_year_09$zp_m30 = NA
master_year_09$zp_m31 = NA
master_year_09$zp_m32 = NA
master_year_09$zp_m33 = NA
master_year_09$zp_m34 = NA
master_year_09$zp_m35 = NA
master_year_09$zp_m36 = NA
master_year_09$zp_m37 = NA

master_year_09 <- dplyr::rename(master_year_09, 
                                hid=hid_zp,
                                res_stat=zp_m1,
                                visit_coo=zp_m2,
                                con_europe=zp_m3,
                                worry_eco_dev=zp12801,
                                worry_eco_sit=zp12802,
                                worry_ret_prov=zp_m4,
                                worry_health=zp12804,
                                worry_env_pro=zp12805,
                                worry_env_change=zp12806,
                                worry_peace=zp12807,
                                worry_crime=zp12809,
                                worry_soc_coh=zp_m5,
                                worry_migration=zp12810,
                                worry_mig_host=zp12811,
                                worry_tech=zp_m6,
                                worry_quali=zp_m7,
                                worry_wlb=zp_m8,
                                worry_sec_work=zp12812,
                                parents_german=zp_m9,
                                one_party=zp123,
                                language_media=zp_m10,
                                refugee_eco=zp_m11,
                                refugee_culture=zp_m12,
                                refugee_ger_living=zp_m13,
                                refugee_risk_short=zp_m14,
                                refugee_risk_long=zp_m15,
                                refugee_donation_lasty=zp_m16,
                                refugee_donation_future=zp_m17,
                                refugee_work_lasty=zp_m18,
                                refugee_work_future=zp_m19,
                                refugee_demo_lasty=zp_m20,
                                refugee_demo_future=zp_m21,
                                visit_coo_dur=zp_m22,
                                work_status=zp09,
                                con_germany=zp_m23,
                                german_citizenship=zp137,                       
                                code_coo=zp_m24,
                                germ_citiz_since_birth=zp139,
                                code_citizenship=zp140,
                                second_citizenship=zp13801,
                                risk_taking=zp121,
                                con_coo=zp_m25,
                                polparty=zp12401,
                                party_intensity=zp12402,
                                political_interest=zp122,
                                satisf_democracy=zp_m26, 
                                pol_soc_engagement=zp_m27,
                                control_life=zp_m28,
                                run_down=zp_m29,
                                well_balanced=zp_m30,
                                doctor_3month_n_visits=zp9701,
                                no_doctor_3month=zp9702,
                                payments_other=zp15417,
                                amount_payment_other=zp15418,
                                others_germany=zp15419,
                                others_foreign=zp15420,
                                current_education=zp_m31,
                                occupational_status=zp_m32,
                                work_abroad=zp_m33,
                                birthyear=zpbirthy,
                                sex=zpsex,
                                no_survey_translation=zp_m34,
                                interview_completion=zpinta,
                                pid=pid,
                                work_home=zp_m35,
                                voting_2018=zp_m36,
                                contact_friends=zp_m37
)

#2008
master_year_08$yp_m1 = NA
master_year_08$yp_m2 = NA
master_year_08$yp_m3 = NA
master_year_08$yp_m4 = NA
master_year_08$yp_m5 = NA
master_year_08$yp_m6 = NA
master_year_08$yp_m7 = NA
master_year_08$yp_m8 = NA
master_year_08$yp_m9 = NA
master_year_08$yp_m10 = NA
master_year_08$yp_m11 = NA
master_year_08$yp_m12 = NA
master_year_08$yp_m13 = NA
master_year_08$yp_m14 = NA
master_year_08$yp_m15 = NA
master_year_08$yp_m16 = NA
master_year_08$yp_m17 = NA
master_year_08$yp_m18 = NA
master_year_08$yp_m19 = NA
master_year_08$yp_m20 = NA
master_year_08$yp_m21 = NA
master_year_08$yp_m22 = NA
master_year_08$yp_m23 = NA
master_year_08$yp_m24 = NA
master_year_08$yp_m25 = NA
master_year_08$yp_m26 = NA
master_year_08$yp_m27 = NA
master_year_08$yp_m28 = NA
master_year_08$yp_m29 = NA
master_year_08$yp_m30 = NA
master_year_08$yp_m31 = NA
master_year_08$yp_m32 = NA
master_year_08$yp_m33 = NA
master_year_08$yp_m34 = NA
master_year_08$yp_m35 = NA
master_year_08$yp_m36 = NA

master_year_08 <- dplyr::rename(master_year_08, 
                                hid=hid_yp,
                                res_stat=yp_m1,
                                visit_coo=yp_m2,
                                con_europe=yp_m3,
                                worry_eco_dev=yp13201,
                                worry_eco_sit=yp13202,
                                worry_ret_prov=yp_m4,
                                worry_health=yp13203,
                                worry_env_pro=yp13204,
                                worry_env_change=yp_m5,
                                worry_peace=yp13205,
                                worry_crime=yp13207,
                                worry_soc_coh=yp_m6,
                                worry_migration=yp13209,
                                worry_mig_host=yp13210,
                                worry_tech=yp_m7,
                                worry_quali=yp_m8,
                                worry_wlb=yp_m9,
                                worry_sec_work=yp13211,
                                parents_german=yp_m10,
                                one_party=yp130,
                                language_media=yp_m11,
                                refugee_eco=yp_m12, 
                                refugee_culture=yp_m13,
                                refugee_ger_living=yp_m14,
                                refugee_risk_short=yp_m15,
                                refugee_risk_long=yp_m16,
                                refugee_donation_lasty=yp_m17,
                                refugee_donation_future=yp_m18,
                                refugee_work_lasty=yp_m19,
                                refugee_work_future=yp_m20,
                                refugee_demo_lasty=yp_m21,
                                refugee_demo_future=yp_m22,
                                visit_coo_dur=yp_m23,
                                work_status=yp19,
                                con_germany=yp_m24,
                                german_citizenship=yp137,                       
                                code_coo=yp_m25,
                                germ_citiz_since_birth=yp139,
                                code_citizenship=yp140,
                                second_citizenship=yp13801,
                                risk_taking=yp10,
                                con_coo=yp_m26,
                                polparty=yp13101,
                                party_intensity=yp13102,
                                political_interest=yp129,
                                satisf_democracy=yp_m27, 
                                pol_soc_engagement=yp_m28,
                                control_life=yp_m29,
                                run_down=yp10202,
                                well_balanced=yp10203,
                                doctor_3month_n_visits=yp11001,
                                no_doctor_3month=yp11002,
                                payments_other=yp153051,
                                amount_payment_other=yp153052,
                                others_germany=yp153053,
                                others_foreign=yp153054,
                                current_education=yp_m30,
                                occupational_status=yp_m31,
                                work_abroad=yp_m32,
                                birthyear=ypbirthy,
                                sex=ypsex,
                                no_survey_translation=yp_m33,
                                interview_completion=ypinta,
                                pid=pid,
                                work_home=yp_m34,
                                voting_2018=yp_m35,
                                contact_friends=yp_m36
)

#2007
master_year_07$xp_m1 = NA
master_year_07$xp_m2 = NA
master_year_07$xp_m3 = NA
master_year_07$xp_m4 = NA
master_year_07$xp_m5 = NA
master_year_07$xp_m6 = NA
master_year_07$xp_m7 = NA
master_year_07$xp_m8 = NA
master_year_07$xp_m9 = NA
master_year_07$xp_m10 = NA
master_year_07$xp_m11 = NA
master_year_07$xp_m12 = NA
master_year_07$xp_m13 = NA
master_year_07$xp_m14 = NA
master_year_07$xp_m15 = NA
master_year_07$xp_m16 = NA
master_year_07$xp_m17 = NA
master_year_07$xp_m18 = NA
master_year_07$xp_m19 = NA
master_year_07$xp_m20 = NA
master_year_07$xp_m21 = NA
master_year_07$xp_m22 = NA
master_year_07$xp_m23 = NA
master_year_07$xp_m24 = NA
master_year_07$xp_m25 = NA
master_year_07$xp_m26 = NA
master_year_07$xp_m27 = NA
master_year_07$xp_m28 = NA
master_year_07$xp_m29 = NA
master_year_07$xp_m30 = NA
master_year_07$xp_m31 = NA
master_year_07$xp_m32 = NA
master_year_07$xp_m33 = NA
master_year_07$xp_m34 = NA
master_year_07$xp_m35 = NA
master_year_07$xp_m36 = NA
master_year_07$xp_m37 = NA
master_year_07$xp_m38 = NA
master_year_07$xp_m39 = NA

master_year_07 <- dplyr::rename(master_year_07, 
                                hid=hid_xp,
                                res_stat=xp_m1,
                                visit_coo=xp_m2,
                                con_europe=xp_m3,
                                worry_eco_dev=xp13001,
                                worry_eco_sit=xp13002,
                                worry_ret_prov=xp_m4,
                                worry_health=xp13003,
                                worry_env_pro=xp13004,
                                worry_env_change=xp_m5,
                                worry_peace=xp13005,
                                worry_crime=xp13006,
                                worry_soc_coh=xp_m6,
                                worry_migration=xp13008,
                                worry_mig_host=xp13009,
                                worry_tech=xp_m7,
                                worry_quali=xp_m8,
                                worry_wlb=xp_m9,
                                worry_sec_work=xp13010,
                                parents_german=xp_m10,
                                one_party=xp128,
                                language_media=xp_m11,
                                refugee_eco=xp_m12,
                                refugee_culture=xp_m13,
                                refugee_ger_living=xp_m14,
                                refugee_risk_short=xp_m15, 
                                refugee_risk_long=xp_m16,
                                refugee_donation_lasty=xp_m17,
                                refugee_donation_future=xp_m18,
                                refugee_work_lasty=xp_m19,
                                refugee_work_future=xp_m20,
                                refugee_demo_lasty=xp_m21,
                                refugee_demo_future=xp_m22,
                                visit_coo_dur=xp_m23,
                                work_status=xp13,
                                con_germany=xp_m24,
                                german_citizenship=xp139,                       
                                code_coo=xp_m25,
                                germ_citiz_since_birth=xp141,
                                code_citizenship=xp142,
                                second_citizenship=xp14001,
                                risk_taking=xp_m26,
                                con_coo=xp_m27,
                                polparty=xp12901,
                                party_intensity=xp12902,
                                political_interest=xp127,
                                satisf_democracy=xp_m28, 
                                pol_soc_engagement=xp_m29,
                                control_life=xp_m30,
                                run_down=xp_m31,
                                well_balanced=xp_m32,
                                doctor_3month_n_visits=xp10001,
                                no_doctor_3month=xp10002,
                                payments_other=xp14717,
                                amount_payment_other=xp14718,
                                others_germany=xp14719,
                                others_foreign=xp14720,
                                current_education=xp_m33,
                                occupational_status=xp_m34,
                                work_abroad=xp_m35,
                                birthyear=xpbirthy,
                                sex=xpsex,
                                no_survey_translation=xp_m36,
                                interview_completion=xpinta,
                                pid=pid,
                                work_home=xp_m37,
                                voting_2018=xp_m38,
                                contact_friends=xp_m39
)

#2006
master_year_06$wp_m1 = NA
master_year_06$wp_m2 = NA
master_year_06$wp_m3 = NA
master_year_06$wp_m4 = NA
master_year_06$wp_m5 = NA
master_year_06$wp_m6 = NA
master_year_06$wp_m7 = NA
master_year_06$wp_m8 = NA
master_year_06$wp_m9 = NA
master_year_06$wp_m10 = NA
master_year_06$wp_m11 = NA
master_year_06$wp_m12 = NA
master_year_06$wp_m13 = NA
master_year_06$wp_m14 = NA
master_year_06$wp_m15 = NA
master_year_06$wp_m16 = NA
master_year_06$wp_m17 = NA
master_year_06$wp_m18 = NA
master_year_06$wp_m19 = NA
master_year_06$wp_m20 = NA
master_year_06$wp_m21 = NA
master_year_06$wp_m22 = NA
master_year_06$wp_m23 = NA
master_year_06$wp_m24 = NA
master_year_06$wp_m25 = NA
master_year_06$wp_m26 = NA
master_year_06$wp_m27 = NA
master_year_06$wp_m28 = NA
master_year_06$wp_m29 = NA
master_year_06$wp_m30 = NA
master_year_06$wp_m31 = NA
master_year_06$wp_m32 = NA
master_year_06$wp_m33 = NA
master_year_06$wp_m34 = NA
master_year_06$wp_m35 = NA
master_year_06$wp_m36 = NA

master_year_06 <- dplyr::rename(master_year_06, 
                                hid=hid_wp,
                                res_stat=wp_m1,
                                visit_coo=wp_m2,
                                con_europe=wp_m3,
                                worry_eco_dev=wp12101,
                                worry_eco_sit=wp12102,
                                worry_ret_prov=wp_m4,
                                worry_health=wp12103,
                                worry_env_pro=wp12104,
                                worry_env_change=wp_m5,
                                worry_peace=wp12105,
                                worry_crime=wp12106,
                                worry_soc_coh=wp_m6,
                                worry_migration=wp12108,
                                worry_mig_host=wp12109,
                                worry_tech=wp_m7,
                                worry_quali=wp_m8,
                                worry_wlb=wp_m9,
                                worry_sec_work=wp12110,
                                parents_german=wp_m10,
                                one_party=wp119,
                                language_media=wp_m11,
                                refugee_eco=wp_m12,
                                refugee_culture=wp_m13,
                                refugee_ger_living=wp_m14,
                                refugee_risk_short=wp_m15,
                                refugee_risk_long=wp_m16,
                                refugee_donation_lasty=wp_m17,
                                refugee_donation_future=wp_m18,
                                refugee_work_lasty=wp_m19,
                                refugee_work_future=wp_m20,
                                refugee_demo_lasty=wp_m21,
                                refugee_demo_future=wp_m22,
                                visit_coo_dur=wp_m23,
                                work_status=wp07,
                                con_germany=wp_m24,
                                german_citizenship=wp127,                       
                                code_coo=wp_m25,
                                germ_citiz_since_birth=wp129,
                                code_citizenship=wp130,
                                second_citizenship=wp12801,
                                risk_taking=wp123,
                                con_coo=wp_m26,
                                polparty=wp12001,
                                party_intensity=wp12002,
                                political_interest=wp118,
                                satisf_democracy= wp_m27,
                                pol_soc_engagement=wp_m28,
                                control_life=wp_m29,
                                run_down=wp9002,
                                well_balanced=wp9003,
                                doctor_3month_n_visits=wp9701,
                                no_doctor_3month=wp9702,
                                payments_other=wp14017,
                                amount_payment_other=wp14018,
                                others_germany=wp14019,
                                others_foreign=wp14020,
                                current_education=wp_m30,
                                occupational_status=wp_m31,
                                work_abroad=wp_m32,
                                birthyear=wpbirthy,
                                sex=wpsex,
                                no_survey_translation=wp_m33,
                                interview_completion=wpinta,
                                pid=pid,
                                work_home=wp_m34,
                                voting_2018=wp_m35,
                                contact_friends=wp_m36
)

master_long <- dplyr::bind_rows(master_year_06, master_year_07, master_year_08,
                                master_year_09, master_year_10, master_year_11,
                                master_year_12, master_year_13, master_year_14,
                                master_year_15, master_year_16, master_year_17,
                                master_year_18, master_year_19, master_year_20)

###################  Bring in the regional dataset  ###################
###################  Bring in the regional dataset  ################### 

#load regional data
#regionl <- readRDS("~/work/COVIDEU_2022-10-04/R-Proj_WP1 _SOEP Stay/Data/wp1/regionl.rds") #At UVA
regionl <- readRDS("~/work/February_2024/R_in/regionl.rds")

#create smaller regional dataset
regionl_red <- regionl %>%
  select(hid, bula, syear, nuts3, kr_utmost, kr_utmnord, kr_uemprate,  kr_foreigner, kr_emprate, 
         kr_popdens, kr_area, kr_population, kr_hhinc, kr_gdp_pem, kr_gdp_pc)

regionl_red = regionl_red %>% mutate(kreis_name = case_when(
  nuts3==267 ~ "Aachen",
  nuts3==296 ~ "Ahrweiler",
  nuts3==131 ~ "Aichach-Friedberg",
  nuts3==40 ~ "Alb-Donau-Kreis",
  nuts3==399 ~ "Altenburger Land",
  nuts3==297 ~ "Altenkirchen (Ww)",
  nuts3==353 ~ "Altmarkkreis Salzwedel",
  nuts3==48 ~ "Altötting",
  nuts3==321 ~ "Alzey-Worms",
  nuts3==80 ~ "Amberg",
  nuts3==83 ~ "Amberg-Sulzbach",
  nuts3==230 ~ "Ammerland",
  nuts3==354 ~ "Anhalt-Bitterfeld",
  nuts3==108 ~ "Ansbach, Kreis",
  nuts3==103 ~ "Ansbach, Stadt",
  nuts3==118 ~ "Aschaffenburg, Kreis",
  nuts3==115 ~ "Aschaffenburg, Stadt",
  nuts3==132 ~ "Augsburg, Kreis",
  nuts3==127 ~ "Augsburg, Stadt",
  nuts3==231 ~ "Aurich",
  nuts3==322 ~ "Bad Dürkheim",
  nuts3==119 ~ "Bad Kissingen",
  nuts3==298 ~ "Bad Kreuznach",
  nuts3==50 ~ "Bad Tölz-Wolfratshausen",
  nuts3==14 ~ "Baden-Baden",
  nuts3==94 ~ "Bamberg, Kreis",
  nuts3==90 ~ "Bamberg, Stadt",
  nuts3==146 ~ "Barnim",
  nuts3==338 ~ "Bautzen",
  nuts3==91 ~ "Bayreuth, Kreis",
  nuts3==95 ~ "Bayreuth, Stadt",
  nuts3==49 ~ "Berchtesgadener Land",
  nuts3==167 ~ "Bergstraße",
  nuts3==141 ~ "Berlin",
  nuts3==307~ "Bernkastel-Wittlich",
  nuts3==41 ~ "Biberach",
  nuts3==276 ~ "Bielefeld",
  nuts3==299 ~ "Birkenfeld",
  nuts3==283 ~ "Bochum",
  nuts3==42 ~ "Bodenseekreis",
  nuts3==257 ~ "Bonn",
  nuts3==271 ~ "Borken",
  nuts3==268 ~ "Bottrop",
  nuts3==142 ~ "Brandenburg an der Havel",
  nuts3==197 ~ "Braunschweig",
  nuts3==27 ~ "Breisgau-Hochschwarzwald",
  nuts3==160 ~ "Bremen",
  nuts3==161 ~ "Bremerhaven",
  nuts3==357 ~ "Burgenlandkreis",
  nuts3==2 ~ "Böblingen",
  nuts3==356 ~ "Börde",
  nuts3==23 ~ "Calw",
  nuts3==214 ~ "Celle",
  nuts3==84 ~ "Cham",
  nuts3==342 ~ "Chemnitz",
  nuts3==232 ~ "Cloppenburg",
  nuts3==96 ~ "Coburg, Kreis",
  nuts3==92 ~ "Coburg, Stadt",
  nuts3==300 ~ "Cochem-Zell",
  nuts3==272 ~ "Coesfeld",
  nuts3==143 ~ "Cottbus",
  nuts3==215 ~ "Cuxhaven",
  nuts3==51 ~ "Dachau",
  nuts3==147 ~ "Dahme-Spreewald",
  nuts3==168 ~ "Darmstadt-Dieburg",
  nuts3==71 ~ "Deggendorf",
  nuts3==225 ~ "Delmenhorst",
  nuts3==350 ~ "Dessau-Roßlau",
  nuts3==207 ~ "Diepholz",
  nuts3==133 ~ "Dillingen a.d.Donau",
  nuts3==79 ~ "Dingolfing-Landau",
  nuts3==368 ~ "Dithmarschen",
  nuts3==139 ~ "Donau-Ries",
  nuts3==323 ~ "Donnersbergkreis",
  nuts3==284 ~ "Dortmund",
  nuts3==337 ~ "Dresden",
  nuts3==243 ~ "Duisburg",
  nuts3==260 ~ "Düren",
  nuts3==242 ~ "Düsseldorf",
  nuts3==52 ~ "Ebersberg",
  nuts3==384 ~ "Eichsfeld",
  nuts3==53 ~ "Eichstätt",
  nuts3==308 ~ "Eifelkreis Bitburg-Prüm",
  nuts3==148 ~ "Elbe-Elster",
  nuts3==226 ~ "Emden",
  nuts3==28 ~ "Emmendingen",
  nuts3==233 ~ "Emsland",
  nuts3==288 ~ "Ennepe-Ruhr-Kreis",
  nuts3==24 ~ "Enzkreis",
  nuts3==54 ~ "Erding",
  nuts3==379 ~ "Erfurt",
  nuts3==104 ~ "Erlangen",
  nuts3==109 ~ "Erlangen-Höchstadt",
  nuts3==343 ~ "Erzgebirgskreis",
  nuts3==244 ~ "Essen",
  nuts3==3 ~ "Esslingen",
  nuts3==262 ~ "Euskirchen",
  nuts3==364 ~ "Flensburg, Stadt",
  nuts3==97 ~ "Forchheim",
  nuts3==144 ~ "Frankfurt (Oder)",
  nuts3==26 ~ "Freiburg im Breisgau",
  nuts3==55 ~ "Freising",
  nuts3==25 ~ "Freudenstadt",
  nuts3==72 ~ "Freyung-Grafenau",
  nuts3==234 ~ "Friesland",
  nuts3==183 ~ "Fulda",
  nuts3==56 ~ "Fürstenfeldbruck",
  nuts3==110 ~ "Fürth, Kreis",
  nuts3==105 ~ "Fürth, Stadt",
  nuts3==57 ~ "Garmisch-Partenkirchen",
  nuts3==269 ~ "Gelsenkirchen",
  nuts3==380 ~ "Gera",
  nuts3==324 ~ "Germersheim",
  nuts3==177 ~ "Gießen",
  nuts3==200 ~ "Gifhorn",
  nuts3==201 ~ "Goslar",
  nuts3==389 ~ "Gotha",
  nuts3==235 ~ "Grafschaft Bentheim",
  nuts3==398 ~ "Greiz",
  nuts3==169 ~ "Groß-Gerau",
  nuts3==4 ~ "Göppingen",
  nuts3==339 ~ "Görlitz",
  nuts3==206 ~ "Göttingen",
  nuts3==134 ~ "Günzburg",
  nuts3==277 ~ "Gütersloh",
  nuts3==285 ~ "Hagen",
  nuts3==351 ~ "Halle (Saale)",
  nuts3==162 ~ "Hamburg",
  nuts3==208 ~ "Hameln-Pyrmont",
  nuts3==286 ~ "Hamm",
  nuts3==213 ~ "Hannover",
  nuts3==216 ~ "Harburg",
  nuts3==358 ~ "Harz",
  nuts3==149 ~ "Havelland",
  nuts3==121 ~ "Haßberge",
  nuts3==221 ~ "Heidekreis",
  nuts3==18 ~ "Heidelberg",
  nuts3==12 ~ "Heidenheim",
  nuts3==7 ~ "Heilbronn, Kreis",
  nuts3==8 ~ "Heilbronn, Stadt",
  nuts3==263 ~ "Heinsberg",
  nuts3==202 ~ "Helmstedt",
  nuts3==278 ~ "Herford",
  nuts3==287 ~ "Herne",
  nuts3==184 ~ "Hersfeld-Rotenburg",
  nuts3==369 ~ "Herzogtum Lauenburg",
  nuts3==391 ~ "Hildburghausen",
  nuts3==209 ~ "Hildesheim",
  nuts3==289 ~ "Hochsauerlandkreis",
  nuts3==170 ~ "Hochtaunus",
  nuts3==98 ~ "Hof, Kreis",
  nuts3==93 ~ "Hof, Stadt",
  nuts3==9 ~ "Hohenlohekreis",
  nuts3==210 ~ "Holzminden",
  nuts3==279 ~ "Höxter",
  nuts3==392 ~ "Ilm-Kreis",
  nuts3==45 ~ "Ingolstadt",
  nuts3==381 ~ "Jena",
  nuts3==355 ~ "Jerichower Land",
  nuts3==325 ~ "Kaiserslautern",
  nuts3==16 ~ "Karlsruhe, Kreis",
  nuts3==15 ~ "Karlsruhe, Stadt",
  nuts3==185 ~ "Kassel",
  nuts3==128 ~ "Kaufbeuren",
  nuts3==73 ~ "Kelheim",
  nuts3==129 ~ "Kempten (Allgäu)",
  nuts3==365 ~ "Kiel, Landeshauptstadt",
  nuts3==122 ~ "Kitzingen",
  nuts3==252 ~ "Kleve",
  nuts3==33 ~ "Konstanz",
  nuts3==245 ~ "Krefeld",
  nuts3==163 ~ "Kreisfreie Stadt Darmstadt",
  nuts3==164 ~ "Kreisfreie Stadt Frankfurt am Main",
  nuts3==182 ~ "Kreisfreie Stadt Kassel",
  nuts3==165 ~ "Kreisfreie Stadt Offenbach am Main",
  nuts3==99 ~ "Kronach",
  nuts3==100 ~ "Kulmbach",
  nuts3==326 ~ "Kusel",
  nuts3==387 ~ "Kyffhäuserkreis",
  nuts3==258 ~ "Köln",
  nuts3==178 ~ "Lahn-Dill",
  nuts3==166 ~ "Landeshauptstadt Wiesbaden",
  nuts3==332 ~ "Landkreis Merzig-Wadern",
  nuts3==333 ~ "Landkreis Neunkirchen",
  nuts3==192 ~ "Landkreis Rostock",
  nuts3==334 ~ "Landkreis Saarlouis",
  nuts3==336 ~ "Landkreis St. Wendel",
  nuts3==58 ~ "Landsberg am Lech",
  nuts3==74 ~ "Landshut, Kreis",
  nuts3==68 ~ "Landshut, Stadt",
  nuts3==236 ~ "Leer",
  nuts3==348 ~ "Leipzig, Kreis",
  nuts3==347 ~ "Leipzig, Stadt",
  nuts3==259 ~ "Leverkusen",
  nuts3==101 ~ "Lichtenfels",
  nuts3==179 ~ "Limburg-Weilburg",
  nuts3==136 ~ "Lindau (Bodensee)",
  nuts3==280 ~ "Lippe",
  nuts3==5 ~ "Ludwigsburg",
  nuts3==196 ~ "Ludwigslust-Parchim",
  nuts3==34 ~ "Lörrach",
  nuts3==366 ~ "Lübeck, Hansestadt",
  nuts3==217 ~ "Lüchow-Dannenberg",
  nuts3==218 ~ "Lüneburg",
  nuts3==352 ~ "Magdeburg",
  nuts3==171 ~ "Main-Kinzig",
  nuts3==124 ~ "Main-Spessart",
  nuts3==11 ~ "Main-Tauber-Kreis",
  nuts3==172 ~ "Main-Taunus",
  nuts3==329 ~ "Mainz-Bingen",
  nuts3==19 ~ "Mannheim",
  nuts3==359 ~ "Mansfeld-Südharz",
  nuts3==180 ~ "Marburg-Biedenkopf",
  nuts3==301 ~ "Mayen-Koblenz",
  nuts3==191 ~ "Mecklenburgische Seenplatte",
  nuts3==340 ~ "Meißen",
  nuts3==130 ~ "Memmingen",
  nuts3==253 ~ "Mettmann",
  nuts3==59 ~ "Miesbach",
  nuts3==123 ~ "Miltenberg",
  nuts3==281 ~ "Minden-Lübbecke",
  nuts3==344 ~ "Mittelsachsen",
  nuts3==150 ~ "Märkisch-Oderland",
  nuts3==290 ~ "Märkischer Kreis",
  nuts3==246 ~ "Mönchengladbach",
  nuts3==60 ~ "Mühldorf a.Inn",
  nuts3==247 ~ "Mülheim an der Ruhr",
  nuts3==61 ~ "München, Kreis",
  nuts3==46 ~ "München, Landeshauptstadt",
  nuts3==270 ~ "Münster",
  nuts3==20 ~ "Neckar-Odenwald-Kreis",
  nuts3==135 ~ "Neu-Ulm",
  nuts3==62 ~ "Neuburg-Schrobenhausen",
  nuts3==85 ~ "Neumarkt i.d.OPf.",
  nuts3==367 ~ "Neumünster, Stadt",
  nuts3==112~ "Neustadt a.d.Aisch-Bad Windsheim",
  nuts3==86 ~ "Neustadt a.d.Waldnaab",
  nuts3==302 ~ "Neuwied",
  nuts3==211 ~ "Nienburg/Weser",
  nuts3==370 ~ "Nordfriesland",
  nuts3==385 ~ "Nordhausen",
  nuts3==349 ~ "Nordsachsen",
  nuts3==194 ~ "Nordwestmecklenburg",
  nuts3==203 ~ "Northeim",
  nuts3==106 ~ "Nürnberg",
  nuts3==111 ~ "Nürnberger Land",
  nuts3==140 ~ "Oberallgäu",
  nuts3==264 ~ "Oberbergischer Kreis",
  nuts3==248 ~ "Oberhausen",
  nuts3==151 ~ "Oberhavel",
  nuts3==152 ~ "Oberspreewald-Lausitz",
  nuts3==173 ~ "Odenwaldkreis",
  nuts3==153 ~ "Oder-Spree",
  nuts3==174 ~ "Offenbach",
  nuts3==237 ~ "Oldenburg, Kreis",
  nuts3==227 ~ "Oldenburg, Stadt",
  nuts3==291 ~ "Olpe",
  nuts3==29 ~ "Ortenaukreis",
  nuts3==238 ~ "Osnabrück, Kreis",
  nuts3==228 ~ "Osnabrück, Stadt",
  nuts3==13 ~ "Ostalbkreis",
  nuts3==137 ~ "Ostallgäu",
  nuts3==219 ~ "Osterholz",
  nuts3==371 ~ "Ostholstein",
  nuts3==154 ~ "Ostprignitz-Ruppin",
  nuts3==282 ~ "Paderborn",
  nuts3==75 ~ "Passau, Kreis",
  nuts3==69 ~ "Passau, Stadt",
  nuts3==303 ~ "Rhein-Hunsrück-Kreis",
  nuts3==254 ~ "Rhein-Kreis Neuss",
  nuts3==304 ~ "Rhein-Lahn-Kreis",
  nuts3==21 ~ "Rhein-Neckar-Kreis",
  nuts3==328 ~ "Rhein-Pfalz-Kreis",
  nuts3==266 ~ "Rhein-Sieg-Kreis",
  nuts3==175 ~ "Rheingau-Taunus",
  nuts3==265 ~ "Rheinisch-Bergischer Kreis",
  nuts3==120 ~ "Rhön-Grabfeld",
  nuts3==64 ~ "Rosenheim, Kreis",
  nuts3==47 ~ "Rosenheim, Stadt",
  nuts3==189 ~ "Rostock, Hansestadt",
  nuts3==220 ~ "Rotenburg (Wümme)",
  nuts3==113 ~ "Roth",
  nuts3==77 ~ "Rottal-Inn",
  nuts3==30 ~ "Rottweil",
  nuts3==396 ~ "Saale-Holzland-Kreis",
  nuts3==397 ~ "Saale-Orla-Kreis",
  nuts3==360 ~ "Saalekreis",
  nuts3==395 ~ "Saalfeld-Rudolstadt",
  nuts3==335 ~ "Saarpfalz-Kreis",
  nuts3==198 ~ "Salzgitter",
  nuts3==361 ~ "Salzlandkreis",
  nuts3==212 ~ "Schaumburg",
  nuts3==375 ~ "Schleswig-Flensburg",
  nuts3==388 ~ "Schmalkalden-Meiningen",
  nuts3==107 ~ "Schwabach",
  nuts3==186 ~ "Schwalm-Eder",
  nuts3==88 ~ "Schwandorf",
  nuts3==31 ~ "Schwarzwald-Baar-Kreis",
  nuts3==125 ~ "Schweinfurt, Kreis",
  nuts3==116 ~ "Schweinfurt, Stadt",
  nuts3==190 ~ "Schwerin, Landeshauptstadt",
  nuts3==10 ~ "Schwäbisch Hall",
  nuts3==376 ~ "Segeberg",
  nuts3==292 ~ "Siegen-Wittgenstein",
  nuts3==44 ~ "Sigmaringen",
  nuts3==293 ~ "Soest",
  nuts3==250 ~ "Solingen",
  nuts3==394 ~ "Sonneberg",
  nuts3==157 ~ "Spree-Neiße",
  nuts3==222 ~ "Stade",
  nuts3==311 ~ "Stadt Frankenthal (Pfalz)",
  nuts3==312 ~ "Stadt Kaiserslautern",
  nuts3==295 ~ "Stadt Koblenz",
  nuts3==313 ~ "Stadt Landau in der Pfalz",
  nuts3==314 ~ "Stadt Ludwigshafen a. Rh.",
  nuts3==315 ~ "Stadt Mainz",
  nuts3==316 ~ "Stadt Neustadt a.d. W.",
  nuts3==317 ~ "Stadt Pirmasens",
  nuts3==318 ~ "Stadt Speyer",
  nuts3==306 ~ "Stadt Trier",
  nuts3==319 ~ "Stadt Worms",
  nuts3==320 ~ "Stadt Zweibrücken",
  nuts3==65 ~ "Starnberg",
  nuts3==377 ~ "Steinburg",
  nuts3==274 ~ "Steinfurt",
  nuts3==362 ~ "Stendal",
  nuts3==378 ~ "Stormarn",
  nuts3==70 ~ "Straubing",
  nuts3==78 ~ "Straubing-Bogen",
  nuts3==1 ~ "Stuttgart",
  nuts3==382 ~ "Suhl",
  nuts3==341 ~ "Sächsische Schweiz-Osterzgebirge",
  nuts3==390 ~ "Sömmerda",
  nuts3==327 ~ "Südliche Weinstraße",
  nuts3==330 ~ "Südwestpfalz",
  nuts3==158 ~ "Teltow-Fläming",
  nuts3==89 ~ "Tirschenreuth",
  nuts3==66 ~ "Traunstein",
  nuts3==310 ~ "Trier-Saarburg",
  nuts3==32 ~ "Tuttlingen",
  nuts3==37 ~ "Tübingen",
  nuts3==159 ~ "Uckermark",
  nuts3==223 ~ "Uelzen",
  nuts3==39 ~ "Ulm",
  nuts3==294 ~ "Unna",
  nuts3==386 ~ "Unstrut-Hainich-Kreis",
  nuts3==138 ~ "Unterallgäu",
  nuts3==239 ~ "Vechta",
  nuts3==224 ~ "Verden",
  nuts3==255 ~ "Viersen",
  nuts3==181 ~ "Vogelsberg",
  nuts3==345 ~ "Vogtlandkreis",
  nuts3==195 ~ "Vorpommern-Greifswald",
  nuts3==193 ~ "Vorpommern-Rügen",
  nuts3==309 ~ "Vulkaneifel",
  nuts3==187 ~ "Waldeck-Frankenberg",
  nuts3==35 ~ "Waldshut",
  nuts3==275 ~ "Warendorf",
  nuts3==401 ~ "Wartburgkreis",
  nuts3==82 ~ "Weiden i.d.OPf.",
  nuts3==67 ~ "Weilheim-Schongau",
  nuts3==383 ~ "Weimar",
  nuts3==393 ~ "Weimarer Land",
  nuts3==114 ~ "Weißenburg-Gunzenhausen",
  nuts3==188 ~ "Werra-Meißner",
  nuts3==256 ~ "Wesel",
  nuts3==240 ~ "Wesermarsch",
  nuts3==305 ~ "Westerwaldkreis",
  nuts3==176 ~ "Wetterau",
  nuts3==229 ~ "Wilhelmshaven",
  nuts3==363 ~ "Wittenberg",
  nuts3==241 ~ "Wittmund",
  nuts3==205 ~ "Wolfenbüttel",
  nuts3==199 ~ "Wolfsburg",
  nuts3==102 ~ "Wunsiedel i.Fichtelgebirge",
  nuts3==251 ~ "Wuppertal",
  nuts3==126 ~ "Würzburg, Kreis",
  nuts3==117 ~ "Würzburg, Stadt",
  nuts3==38 ~ "Zollernalbkreis",
  nuts3==346 ~ "Zwickau",
  nuts3==400 ~ "Eisenach, Kreisfreie Stadt"
))

regionl_red_2020 = regionl_red %>%
  filter(syear == 2020)
regionl_red_2019 = regionl_red %>%
  filter(syear == 2019)
regionl_red_2018 = regionl_red %>%
  filter(syear == 2018)
regionl_red_2017 = regionl_red %>%
  filter(syear == 2017)
regionl_red_2016 = regionl_red %>%
  filter(syear == 2016)
regionl_red_2015 = regionl_red %>%
  filter(syear == 2015)
regionl_red_2014 = regionl_red %>%
  filter(syear == 2014)
regionl_red_2013 = regionl_red %>%
  filter(syear == 2013)
regionl_red_2012 = regionl_red %>%
  filter(syear == 2012)
regionl_red_2011 = regionl_red %>%
  filter(syear == 2011)
regionl_red_2010 = regionl_red %>%
  filter(syear == 2010)
regionl_red_2009 = regionl_red %>%
  filter(syear == 2009)
regionl_red_2008 = regionl_red %>%
  filter(syear == 2008)
regionl_red_2007 = regionl_red %>%
  filter(syear == 2007)
regionl_red_2006 = regionl_red %>%
  filter(syear == 2006)

#1.1 merge with regional data
reg_master_06 <- list(master_year_06, regionl_red_2006) %>%
  reduce(left_join, by="hid")
reg_master_07 <- list(master_year_07, regionl_red_2007) %>%
  reduce(left_join, by="hid")
reg_master_08 <- list(master_year_08, regionl_red_2008) %>%
  reduce(left_join, by="hid")
reg_master_09 <- list(master_year_09, regionl_red_2009) %>%
  reduce(left_join, by="hid")
reg_master_10 <- list(master_year_10, regionl_red_2010) %>%
  reduce(left_join, by="hid")
reg_master_11 <- list(master_year_11, regionl_red_2011) %>%
  reduce(left_join, by="hid")
reg_master_12 <- list(master_year_12, regionl_red_2012) %>%
  reduce(left_join, by="hid")
reg_master_13 <- list(master_year_13, regionl_red_2013) %>%
  reduce(left_join, by="hid")
reg_master_14 <- list(master_year_14, regionl_red_2014) %>%
  reduce(left_join, by="hid")
reg_master_15 <- list(master_year_15, regionl_red_2015) %>%
  reduce(left_join, by="hid")
reg_master_16 <- list(master_year_16, regionl_red_2016) %>%
  reduce(left_join, by="hid")
reg_master_17 <- list(master_year_17, regionl_red_2017) %>%
  reduce(left_join, by="hid")
reg_master_18 <- list(master_year_18, regionl_red_2018) %>%
  reduce(left_join, by="hid")
reg_master_19 <- list(master_year_19, regionl_red_2019) %>%
  reduce(left_join, by="hid")
reg_master_20 <- list(master_year_20, regionl_red_2020) %>%
  reduce(left_join, by="hid")

df_long <- dplyr::bind_rows(reg_master_06, reg_master_07, reg_master_08,
                            reg_master_09, reg_master_10, reg_master_11,
                            reg_master_12, reg_master_13, reg_master_14,
                            reg_master_15, reg_master_16, reg_master_17,
                            reg_master_18, reg_master_19, reg_master_20)

#########################################OWN DATA IMPORT#######################################################
#########################################OWN DATA IMPORT#######################################################
# Distances to border based on Kreise
library(readr)
#Kreise_distance_to_borderGEN <- read_csv(file.path(path_in, "Kreise_distance-to-border.csv"))
Kreise_distance_to_borderGEN <- read_csv("~/work/April_2024/R_in/Kreise_distance-to-border-and-nearest-countryCZECHIA.csv")
#new variable with kreis name and type (e.g. Landkreis, Stadt, etc.)

unique(Kreise_distance_to_borderGEN$GEN)

Kreise_distance_to_borderGEN$GEN <- gsub("<fc>", "ü", gsub("<f6>", "ö", gsub("<e4>", "ä", gsub("<df>", "ß", Kreise_distance_to_borderGEN$GEN))))
unique(Kreise_distance_to_borderGEN$GEN)

# Now bring in the NEW borders dataset
library(readr)
NEW_border_closures <- read_csv("~/work/2023-02-24/R_in/final_border_closure_data.csv")
names(NEW_border_closures)

#NEW_border_closures$newdate <- format(as.Date(NEW_border_closures$date, "%m/%d/%Y"),"%Y-%m-%d")
NEW_border_closures$newdate <- format(as.Date(NEW_border_closures$date, "%m/%d/%Y"))
NEW_border_closures$newweekyear <- format(as.Date(NEW_border_closures$newdate), "%Y-%W")

#Replace newweekyear 2020-00 with 2019-52 
NEW_border_closures$newweekyear[NEW_border_closures$newweekyear=="2020-00"]="2019-52"

# Now bring in the OLD borders dataset
library(haven)
border_df <- readRDS("~/work/2023-02-24/R_in/oxford-kreis-treatment.rds")

border_df$kreis <- gsub("Ã¶", "ö", gsub("ÃŸ", "ß", gsub("Ã¤", "ä", gsub("Ãœ", "Ü", gsub("Ã¼", "ü", border_df$kreis)))))
sort(border_df$kreis)

border_df$kreis[border_df$n_kreis==	1	]=	"Aachen"
border_df$kreis[border_df$n_kreis==	2	]=	"Ahrweiler"
border_df$kreis[border_df$n_kreis==	3	]=	"Aichach-Friedberg"
border_df$kreis[border_df$n_kreis==	4	]=	"Alb-Donau-Kreis"
border_df$kreis[border_df$n_kreis==	5	]=	"Altenburger Land"
border_df$kreis[border_df$n_kreis==	6	]=	"Altenkirchen (Ww)"
border_df$kreis[border_df$n_kreis==	7	]=	"Altmarkkreis Salzwedel"
border_df$kreis[border_df$n_kreis==	8	]=	"Altötting"
border_df$kreis[border_df$n_kreis==	9	]=	"Alzey-Worms"
border_df$kreis[border_df$n_kreis==	10	]=	"Amberg"
border_df$kreis[border_df$n_kreis==	11	]=	"Amberg-Sulzbach"
border_df$kreis[border_df$n_kreis==	12	]=	"Ammerland"
border_df$kreis[border_df$n_kreis==	13	]=	"Anhalt-Bitterfeld"
border_df$kreis[border_df$n_kreis==	14	]=	"Ansbach, Kreis"
border_df$kreis[border_df$n_kreis==	15	]=	"Ansbach, Stadt"
border_df$kreis[border_df$n_kreis==	16	]=	"Aschaffenburg, Kreis"
border_df$kreis[border_df$n_kreis==	17	]=	"Aschaffenburg, Stadt"
border_df$kreis[border_df$n_kreis==	18	]=	"Augsburg, Kreis"
border_df$kreis[border_df$n_kreis==	19	]=	"Augsburg, Stadt"
border_df$kreis[border_df$n_kreis==	20	]=	"Aurich"
border_df$kreis[border_df$n_kreis==	21	]=	"Bad Dürkheim"
border_df$kreis[border_df$n_kreis==	22	]=	"Bad Kissingen"
border_df$kreis[border_df$n_kreis==	23	]=	"Bad Kreuznach"
border_df$kreis[border_df$n_kreis==	24	]=	"Bad Tölz-Wolfratshausen"
border_df$kreis[border_df$n_kreis==	25	]=	"Baden-Baden"
border_df$kreis[border_df$n_kreis==	26	]=	"Bamberg, Kreis"
border_df$kreis[border_df$n_kreis==	27	]=	"Bamberg, Stadt"
border_df$kreis[border_df$n_kreis==	28	]=	"Barnim"
border_df$kreis[border_df$n_kreis==	29	]=	"Bautzen"
border_df$kreis[border_df$n_kreis==	30	]=	"Bayreuth, Kreis"
border_df$kreis[border_df$n_kreis==	31	]=	"Bayreuth, Stadt"
border_df$kreis[border_df$n_kreis==	32	]= "Berchtesgadener Land"
border_df$kreis[border_df$n_kreis==	33	]=	"Bergstraße"
border_df$kreis[border_df$n_kreis==	34	]=	"Berlin"
border_df$kreis[border_df$n_kreis==	35	]=	"Bernkastel-Wittlich"
border_df$kreis[border_df$n_kreis==	36	]=	"Biberach"
border_df$kreis[border_df$n_kreis==	37	]=	"Bielefeld"
border_df$kreis[border_df$n_kreis==	38	]=	"Birkenfeld"
border_df$kreis[border_df$n_kreis==	39	]=	"Bochum"
border_df$kreis[border_df$n_kreis==	40	]=	"Bodenseekreis"
border_df$kreis[border_df$n_kreis==	41	]=	"Bonn"
border_df$kreis[border_df$n_kreis==	42	]=	"Borken"
border_df$kreis[border_df$n_kreis==	43	]=	"Bottrop"
border_df$kreis[border_df$n_kreis==	44	]=	"Brandenburg an der Havel"
border_df$kreis[border_df$n_kreis==	45	]=	"Braunschweig"
border_df$kreis[border_df$n_kreis==	46	]=	"Breisgau-Hochschwarzwald"
border_df$kreis[border_df$n_kreis==	47	]=	"Bremen"
border_df$kreis[border_df$n_kreis==	48	]=	"Bremerhaven"
border_df$kreis[border_df$n_kreis==	49	]=	"Burgenlandkreis"
border_df$kreis[border_df$n_kreis==	50	]=	"Böblingen"
border_df$kreis[border_df$n_kreis==	51	]=	"Börde"
border_df$kreis[border_df$n_kreis==	52	]=	"Calw"
border_df$kreis[border_df$n_kreis==	53	]=	"Celle"
border_df$kreis[border_df$n_kreis==	54	]=	"Cham"
border_df$kreis[border_df$n_kreis==	55	]=	"Chemnitz"
border_df$kreis[border_df$n_kreis==	56	]=	"Cloppenburg"
border_df$kreis[border_df$n_kreis==	57	]=	"Coburg, Kreis"
border_df$kreis[border_df$n_kreis==	58	]=	"Coburg, Stadt"
border_df$kreis[border_df$n_kreis==	59	]=	"Cochem-Zell"
border_df$kreis[border_df$n_kreis==	60	]=	"Coesfeld"
border_df$kreis[border_df$n_kreis==	61	]=	"Cottbus"
border_df$kreis[border_df$n_kreis==	62	]=	"Cuxhaven"
border_df$kreis[border_df$n_kreis==	63	]=	"Dachau"
border_df$kreis[border_df$n_kreis==	64	]=	"Dahme-Spreewald"
border_df$kreis[border_df$n_kreis==	65	]=	"Darmstadt-Dieburg"
border_df$kreis[border_df$n_kreis==	66	]=	"Deggendorf"
border_df$kreis[border_df$n_kreis==	67	]=	"Delmenhorst"
border_df$kreis[border_df$n_kreis==	68	]=	"Dessau-Roßlau"
border_df$kreis[border_df$n_kreis==	69	]=	"Diepholz"
border_df$kreis[border_df$n_kreis==	70	]=	"Dillingen a.d.Donau"
border_df$kreis[border_df$n_kreis==	71	]=	"Dingolfing-Landau"
border_df$kreis[border_df$n_kreis==	72	]=	"Dithmarschen"
border_df$kreis[border_df$n_kreis==	73	]=	"Donau-Ries"
border_df$kreis[border_df$n_kreis==	74	]=	"Donnersbergkreis"
border_df$kreis[border_df$n_kreis==	75	]=	"Dortmund"
border_df$kreis[border_df$n_kreis==	76	]=	"Dresden"
border_df$kreis[border_df$n_kreis==	77	]=	"Duisburg"
border_df$kreis[border_df$n_kreis==	78	]=	"Düren"
border_df$kreis[border_df$n_kreis==	79	]=	"Düsseldorf"
border_df$kreis[border_df$n_kreis==	80	]=	"Ebersberg"
border_df$kreis[border_df$n_kreis==	81	]=	"Eichsfeld"
border_df$kreis[border_df$n_kreis==	82	]=	"Eichstätt"
border_df$kreis[border_df$n_kreis==	83	]=	"Eifelkreis Bitburg-Prüm"
border_df$kreis[border_df$n_kreis==	84	]=  "Elbe-Elster"
border_df$kreis[border_df$n_kreis==	85	]=	"Emden"
border_df$kreis[border_df$n_kreis==	86	]=	"Emmendingen"
border_df$kreis[border_df$n_kreis==	87	]=	"Emsland"
border_df$kreis[border_df$n_kreis==	88	]=	"Ennepe-Ruhr-Kreis"
border_df$kreis[border_df$n_kreis==	89	]=	"Enzkreis"
border_df$kreis[border_df$n_kreis==	90	]=	"Erding"
border_df$kreis[border_df$n_kreis==	91	]=	"Erfurt"
border_df$kreis[border_df$n_kreis==	92	]=	"Erlangen"
border_df$kreis[border_df$n_kreis==	93	]=	"Erlangen-Höchstadt"
border_df$kreis[border_df$n_kreis==	94	]=	"Erzgebirgskreis"
border_df$kreis[border_df$n_kreis==	95	]=	"Essen"
border_df$kreis[border_df$n_kreis==	96	]=	"Esslingen"
border_df$kreis[border_df$n_kreis==	97	]=	"Euskirchen"
border_df$kreis[border_df$n_kreis==	98	]=	"Flensburg, Stadt"
border_df$kreis[border_df$n_kreis==	99	]=	"Forchheim"
border_df$kreis[border_df$n_kreis==	100	]=	"Frankfurt (Oder)"
border_df$kreis[border_df$n_kreis==	101	]=	"Freiburg im Breisgau"
border_df$kreis[border_df$n_kreis==	102	]=	"Freising"
border_df$kreis[border_df$n_kreis==	103	]=	"Freudenstadt"
border_df$kreis[border_df$n_kreis==	104	]=	"Freyung-Grafenau"
border_df$kreis[border_df$n_kreis==	105	]=	"Friesland"
border_df$kreis[border_df$n_kreis==	106	]=	"Fulda"
border_df$kreis[border_df$n_kreis==	107	]=	"Fürstenfeldbruck"
border_df$kreis[border_df$n_kreis==	108	]=	"Fürth, Kreis"
border_df$kreis[border_df$n_kreis==	109	]=	"Fürth, Stadt"
border_df$kreis[border_df$n_kreis==	110	]=	"Garmisch-Partenkirchen"
border_df$kreis[border_df$n_kreis==	111	]=	"Gelsenkirchen"
border_df$kreis[border_df$n_kreis==	112	]=	"Gera"
border_df$kreis[border_df$n_kreis==	113	]=	"Germersheim"
border_df$kreis[border_df$n_kreis==	114	]=	"Gießen"
border_df$kreis[border_df$n_kreis==	115	]=	"Gifhorn"
border_df$kreis[border_df$n_kreis==	116	]=	"Goslar"
border_df$kreis[border_df$n_kreis==	117	]=	"Gotha"
border_df$kreis[border_df$n_kreis==	118	]=	"Grafschaft Bentheim"
border_df$kreis[border_df$n_kreis==	119	]=	"Greiz"
border_df$kreis[border_df$n_kreis==	120	]=	"Groß-Gerau"
border_df$kreis[border_df$n_kreis==	121	]=	"Göppingen"
border_df$kreis[border_df$n_kreis==	122	]=	"Görlitz"
border_df$kreis[border_df$n_kreis==	123	]=	"Göttingen"
border_df$kreis[border_df$n_kreis==	124	]=	"Günzburg"
border_df$kreis[border_df$n_kreis==	125	]=	"Gütersloh"
border_df$kreis[border_df$n_kreis==	126	]=	"Hagen"
border_df$kreis[border_df$n_kreis==	127	]=	"Halle (Saale)"
border_df$kreis[border_df$n_kreis==	128	]=	"Hamburg"
border_df$kreis[border_df$n_kreis==	129	]=	"Hameln-Pyrmont"
border_df$kreis[border_df$n_kreis==	130	]=	"Hamm"
border_df$kreis[border_df$n_kreis==	131	]=	"Hannover"
border_df$kreis[border_df$n_kreis==	132	]=	"Harburg"
border_df$kreis[border_df$n_kreis==	133	]=	"Harz"
border_df$kreis[border_df$n_kreis==	134	]=	"Havelland"
border_df$kreis[border_df$n_kreis==	135	]=	"Haßberge"
border_df$kreis[border_df$n_kreis==	136	]=	"Heidekreis"
border_df$kreis[border_df$n_kreis==	137	]=	"Heidelberg"
border_df$kreis[border_df$n_kreis==	138	]=	"Heidenheim"
border_df$kreis[border_df$n_kreis==	139	]=	"Heilbronn, Kreis"
border_df$kreis[border_df$n_kreis==	140	]=	"Heilbronn, Stadt"
border_df$kreis[border_df$n_kreis==	141	]=	"Heinsberg"
border_df$kreis[border_df$n_kreis==	142	]=	"Helmstedt"
border_df$kreis[border_df$n_kreis==	143	]=	"Herford"
border_df$kreis[border_df$n_kreis==	144	]=	"Herne"
border_df$kreis[border_df$n_kreis==	145	]=	"Hersfeld-Rotenburg"
border_df$kreis[border_df$n_kreis==	146	]=	"Herzogtum Lauenburg"
border_df$kreis[border_df$n_kreis==	147	]=	"Hildburghausen"
border_df$kreis[border_df$n_kreis==	148	]=	"Hildesheim"
border_df$kreis[border_df$n_kreis==	149	]=	"Hochsauerlandkreis"
border_df$kreis[border_df$n_kreis==	150	]=	"Hochtaunus"
border_df$kreis[border_df$n_kreis==	151	]=	"Hof, Kreis"
border_df$kreis[border_df$n_kreis==	152	]=	"Hof, Stadt"
border_df$kreis[border_df$n_kreis==	153	]=	"Hohenlohekreis"
border_df$kreis[border_df$n_kreis==	154	]=	"Holzminden"
border_df$kreis[border_df$n_kreis==	155	]=	"Höxter"
border_df$kreis[border_df$n_kreis==	156	]=	"Ilm-Kreis"
border_df$kreis[border_df$n_kreis==	157	]=	"Ingolstadt"
border_df$kreis[border_df$n_kreis==	158	]=	"Jena"
border_df$kreis[border_df$n_kreis==	159	]=	"Jerichower Land"
border_df$kreis[border_df$n_kreis==	160	]=	"Kaiserslautern"
border_df$kreis[border_df$n_kreis==	161	]=	"Karlsruhe, Kreis"
border_df$kreis[border_df$n_kreis==	162	]=	"Karlsruhe, Stadt"
border_df$kreis[border_df$n_kreis==	163	]=	"Kassel"
border_df$kreis[border_df$n_kreis==	164	]=	"Kaufbeuren"
border_df$kreis[border_df$n_kreis==	165	]=	"Kelheim"
border_df$kreis[border_df$n_kreis==	166	]=	"Kempten (Allgäu)"
border_df$kreis[border_df$n_kreis==	167	]=	"Kiel, Landeshauptstadt"
border_df$kreis[border_df$n_kreis==	168	]=	"Kitzingen"
border_df$kreis[border_df$n_kreis==	169	]=	"Kleve"
border_df$kreis[border_df$n_kreis==	170	]=	"Konstanz"
border_df$kreis[border_df$n_kreis==	171	]=	"Krefeld"
border_df$kreis[border_df$n_kreis==	172	]=	"Kreisfreie Stadt Darmstadt"
border_df$kreis[border_df$n_kreis==	173	]=	"Kreisfreie Stadt Frankfurt am Main"
border_df$kreis[border_df$n_kreis==	174	]=	"Kreisfreie Stadt Kassel"
border_df$kreis[border_df$n_kreis==	175	]=	"Kreisfreie Stadt Offenbach am Main"
border_df$kreis[border_df$n_kreis==	176	]=	"Kronach"
border_df$kreis[border_df$n_kreis==	177	]=	"Kulmbach"
border_df$kreis[border_df$n_kreis==	178	]=	"Kusel"
border_df$kreis[border_df$n_kreis==	179	]=	"Kyffhäuserkreis"
border_df$kreis[border_df$n_kreis==	180	]=	"Köln"
border_df$kreis[border_df$n_kreis==	181	]=	"Lahn-Dill"
border_df$kreis[border_df$n_kreis==	182	]=	"Landeshauptstadt Wiesbaden"
border_df$kreis[border_df$n_kreis==	183	]=	"Landkreis Merzig-Wadern"
border_df$kreis[border_df$n_kreis==	184	]=	"Landkreis Neunkirchen"
border_df$kreis[border_df$n_kreis==	185	]=	"Landkreis Rostock"
border_df$kreis[border_df$n_kreis==	186	]=	"Landkreis Saarlouis"
border_df$kreis[border_df$n_kreis==	187	]=	"Landkreis St. Wendel"
border_df$kreis[border_df$n_kreis==	188	]=  "Landsberg am Lech"
border_df$kreis[border_df$n_kreis==	189	]=	"Landshut, Kreis"
border_df$kreis[border_df$n_kreis==	190	]=	"Landshut, Stadt"
border_df$kreis[border_df$n_kreis==	191	]=	"Leer"
border_df$kreis[border_df$n_kreis==	192	]=	"Leipzig, Kreis"
border_df$kreis[border_df$n_kreis==	193	]=	"Leipzig, Stadt"
border_df$kreis[border_df$n_kreis==	194	]=	"Leverkusen"
border_df$kreis[border_df$n_kreis==	195	]=	"Lichtenfels"
border_df$kreis[border_df$n_kreis==	196	]=	"Limburg-Weilburg"
border_df$kreis[border_df$n_kreis==	197	]=	"Lindau (Bodensee)"
border_df$kreis[border_df$n_kreis==	198	]=	"Lippe"
border_df$kreis[border_df$n_kreis==	199	]=	"Ludwigsburg"
border_df$kreis[border_df$n_kreis==	200	]=	"Ludwigslust-Parchim"
border_df$kreis[border_df$n_kreis==	201	]=	"Lörrach"
border_df$kreis[border_df$n_kreis==	202	]=	"Lübeck, Hansestadt"
border_df$kreis[border_df$n_kreis==	203	]=	"Lüchow-Dannenberg"
border_df$kreis[border_df$n_kreis==	204	]=	"Lüneburg"
border_df$kreis[border_df$n_kreis==	205	]=	"Magdeburg"
border_df$kreis[border_df$n_kreis==	206	]=	"Main-Kinzig"
border_df$kreis[border_df$n_kreis==	207	]=	"Main-Spessart"
border_df$kreis[border_df$n_kreis==	208	]=	"Main-Tauber-Kreis"
border_df$kreis[border_df$n_kreis==	209	]=	"Main-Taunus"
border_df$kreis[border_df$n_kreis==	210	]=	"Mainz-Bingen"
border_df$kreis[border_df$n_kreis==	211	]=	"Mannheim"
border_df$kreis[border_df$n_kreis==	212	]=	"Mansfeld-Südharz"
border_df$kreis[border_df$n_kreis==	213	]=	"Marburg-Biedenkopf"
border_df$kreis[border_df$n_kreis==	214	]=	"Mayen-Koblenz"
border_df$kreis[border_df$n_kreis==	215	]=	"Mecklenburgische Seenplatte"
border_df$kreis[border_df$n_kreis==	216	]=	"Meißen"
border_df$kreis[border_df$n_kreis==	217	]=	"Memmingen"
border_df$kreis[border_df$n_kreis==	218	]=	"Mettmann"
border_df$kreis[border_df$n_kreis==	219	]=	"Miesbach"
border_df$kreis[border_df$n_kreis==	220	]=	"Miltenberg"
border_df$kreis[border_df$n_kreis==	221	]=	"Minden-Lübbecke"
border_df$kreis[border_df$n_kreis==	222	]=	"Mittelsachsen"
border_df$kreis[border_df$n_kreis==	223	]=	"Märkisch-Oderland"
border_df$kreis[border_df$n_kreis==	224	]=	"Märkischer Kreis"
border_df$kreis[border_df$n_kreis==	225	]=	"Mönchengladbach"
border_df$kreis[border_df$n_kreis==	226	]=	"Mühldorf a.Inn"
border_df$kreis[border_df$n_kreis==	227	]=	"Mülheim an der Ruhr"
border_df$kreis[border_df$n_kreis==	228	]=	"München, Kreis"
border_df$kreis[border_df$n_kreis==	229	]=	"München, Landeshauptstadt"
border_df$kreis[border_df$n_kreis==	230	]=	"Münster"
border_df$kreis[border_df$n_kreis==	231	]=	"Neckar-Odenwald-Kreis"
border_df$kreis[border_df$n_kreis==	232	]=	"Neu-Ulm"
border_df$kreis[border_df$n_kreis==	233	]=	"Neuburg-Schrobenhausen"
border_df$kreis[border_df$n_kreis==	234	]=	"Neumarkt i.d.OPf."
border_df$kreis[border_df$n_kreis==	235	]=	"Neumünster, Stadt"
border_df$kreis[border_df$n_kreis==	236	]=	"Neustadt a.d.Aisch-Bad Windsheim"
border_df$kreis[border_df$n_kreis==	237	]=	"Neustadt a.d.Waldnaab"
border_df$kreis[border_df$n_kreis==	238	]=	"Neuwied"
border_df$kreis[border_df$n_kreis==	239	]=	"Nienburg/Weser"
border_df$kreis[border_df$n_kreis==	240	]=	"Nordfriesland"
border_df$kreis[border_df$n_kreis==	241	]=	"Nordhausen"
border_df$kreis[border_df$n_kreis==	242	]=	"Nordsachsen"
border_df$kreis[border_df$n_kreis==	243	]=	"Nordwestmecklenburg"
border_df$kreis[border_df$n_kreis==	244	]=	"Northeim"
border_df$kreis[border_df$n_kreis==	245	]=	"Nürnberg"
border_df$kreis[border_df$n_kreis==	246	]=	"Nürnberger Land"
border_df$kreis[border_df$n_kreis==	247	]=	"Oberallgäu"
border_df$kreis[border_df$n_kreis==	248	]=	"Oberbergischer Kreis"
border_df$kreis[border_df$n_kreis==	249	]=	"Oberhausen"
border_df$kreis[border_df$n_kreis==	250	]=	"Oberhavel"
border_df$kreis[border_df$n_kreis==	251	]=	"Oberspreewald-Lausitz"
border_df$kreis[border_df$n_kreis==	252	]=	"Odenwaldkreis"
border_df$kreis[border_df$n_kreis==	253	]=	"Oder-Spree"
border_df$kreis[border_df$n_kreis==	254	]=	"Offenbach"
border_df$kreis[border_df$n_kreis==	255	]=	"Oldenburg, Kreis"
border_df$kreis[border_df$n_kreis==	256	]=	"Oldenburg, Stadt"
border_df$kreis[border_df$n_kreis==	257	]=	"Olpe"
border_df$kreis[border_df$n_kreis==	258	]=	"Ortenaukreis"
border_df$kreis[border_df$n_kreis==	259	]=	"Osnabrück, Kreis"
border_df$kreis[border_df$n_kreis==	260	]=	"Osnabrück, Stadt"
border_df$kreis[border_df$n_kreis==	261	]=	"Ostalbkreis"
border_df$kreis[border_df$n_kreis==	262	]=	"Ostallgäu"
border_df$kreis[border_df$n_kreis==	263	]=	"Osterholz"
border_df$kreis[border_df$n_kreis==	264	]=	"Ostholstein"
border_df$kreis[border_df$n_kreis==	265	]=	"Ostprignitz-Ruppin"
border_df$kreis[border_df$n_kreis==	266	]=	"Paderborn"
border_df$kreis[border_df$n_kreis==	267	]=	"Passau, Kreis"
border_df$kreis[border_df$n_kreis==	268	]=	"Passau, Stadt"
border_df$kreis[border_df$n_kreis==	269	]=	"Peine"
border_df$kreis[border_df$n_kreis==	270	]=	"Pfaffenhofen a.d.Ilm"
border_df$kreis[border_df$n_kreis==	271	]=	"Pforzheim"
border_df$kreis[border_df$n_kreis==	272	]=	"Pinneberg"
border_df$kreis[border_df$n_kreis==	273	]=	"Plön"
border_df$kreis[border_df$n_kreis==	274	]=	"Potsdam"
border_df$kreis[border_df$n_kreis==	275	]=	"Potsdam-Mittelmark"
border_df$kreis[border_df$n_kreis==	276	]=	"Prignitz"
border_df$kreis[border_df$n_kreis==	277	]=	"Rastatt"
border_df$kreis[border_df$n_kreis==	278	]=	"Ravensburg"
border_df$kreis[border_df$n_kreis==	279	]=	"Recklinghausen"
border_df$kreis[border_df$n_kreis==	280	]=	"Regen"
border_df$kreis[border_df$n_kreis==	281	]=	"Regensburg, Kreis"
border_df$kreis[border_df$n_kreis==	282	]=	"Regensburg, Stadt"
border_df$kreis[border_df$n_kreis==	283	]=	"Regionalverband Saarbrücken"
border_df$kreis[border_df$n_kreis==	284	]=	"Rems-Murr-Kreis"
border_df$kreis[border_df$n_kreis==	285	]=	"Remscheid"
border_df$kreis[border_df$n_kreis==	286	]=	"Rendsburg-Eckernförde"
border_df$kreis[border_df$n_kreis==	287	]=	"Reutlingen"
border_df$kreis[border_df$n_kreis==	288	]=	"Rhein-Erft-Kreis"
border_df$kreis[border_df$n_kreis==	289	]=	"Rhein-Hunsrück-Kreis"
border_df$kreis[border_df$n_kreis==	290	]=	"Rhein-Kreis Neuss"
border_df$kreis[border_df$n_kreis==	291	]=	"Rhein-Lahn-Kreis"
border_df$kreis[border_df$n_kreis==	292	]=	"Rhein-Neckar-Kreis"
border_df$kreis[border_df$n_kreis==	293	]=	"Rhein-Pfalz-Kreis"
border_df$kreis[border_df$n_kreis==	294	]=	"Rhein-Sieg-Kreis"
border_df$kreis[border_df$n_kreis==	295	]=	"Rheingau-Taunus"
border_df$kreis[border_df$n_kreis==	296	]=	"Rheinisch-Bergischer Kreis"
border_df$kreis[border_df$n_kreis==	297	]=	"Rhön-Grabfeld"
border_df$kreis[border_df$n_kreis==	298	]=	"Rosenheim, Kreis"
border_df$kreis[border_df$n_kreis==	299	]=	"Rosenheim, Stadt"
border_df$kreis[border_df$n_kreis==	300	]=	"Rostock, Hansestadt"
border_df$kreis[border_df$n_kreis==	301	]=	"Rotenburg (Wümme)"
border_df$kreis[border_df$n_kreis==	302	]=	"Roth"
border_df$kreis[border_df$n_kreis==	303	]=	"Rottal-Inn"
border_df$kreis[border_df$n_kreis==	304	]=	"Rottweil"
border_df$kreis[border_df$n_kreis==	305	]=	"Saale-Holzland-Kreis"
border_df$kreis[border_df$n_kreis==	306	]=	"Saale-Orla-Kreis"
border_df$kreis[border_df$n_kreis==	307	]=	"Saalekreis"
border_df$kreis[border_df$n_kreis==	308	]=	"Saalfeld-Rudolstadt"
border_df$kreis[border_df$n_kreis==	309	]=	"Saarpfalz-Kreis"
border_df$kreis[border_df$n_kreis==	310	]=	"Salzgitter"
border_df$kreis[border_df$n_kreis==	311	]=	"Salzlandkreis"
border_df$kreis[border_df$n_kreis==	312	]=	"Schaumburg"
border_df$kreis[border_df$n_kreis==	313	]=	"Schleswig-Flensburg"
border_df$kreis[border_df$n_kreis==	314	]=	"Schmalkalden-Meiningen"
border_df$kreis[border_df$n_kreis==	315	]=	"Schwabach"
border_df$kreis[border_df$n_kreis==	316	]=	"Schwalm-Eder"
border_df$kreis[border_df$n_kreis==	317	]=	"Schwandorf"
border_df$kreis[border_df$n_kreis==	318	]=	"Schwarzwald-Baar-Kreis"
border_df$kreis[border_df$n_kreis==	319	]=	"Schweinfurt, Kreis"
border_df$kreis[border_df$n_kreis==	320	]=	"Schweinfurt, Stadt"
border_df$kreis[border_df$n_kreis==	321	]=	"Schwerin, Landeshauptstadt"
border_df$kreis[border_df$n_kreis==	322	]=	"Schwäbisch Hall"
border_df$kreis[border_df$n_kreis==	323	]=	"Segeberg"
border_df$kreis[border_df$n_kreis==	324	]=	"Siegen-Wittgenstein"
border_df$kreis[border_df$n_kreis==	325	]=	"Sigmaringen"
border_df$kreis[border_df$n_kreis==	326	]=	"Soest"
border_df$kreis[border_df$n_kreis==	327	]=	"Solingen"
border_df$kreis[border_df$n_kreis==	328	]=	"Sonneberg"
border_df$kreis[border_df$n_kreis==	329	]=	"Spree-Neiße"
border_df$kreis[border_df$n_kreis==	330	]=	"Stade"
border_df$kreis[border_df$n_kreis==	331	]=	"Stadt Frankenthal (Pfalz)"
border_df$kreis[border_df$n_kreis==	332	]=	"Stadt Kaiserslautern"
border_df$kreis[border_df$n_kreis==	333	]=	"Stadt Koblenz"
border_df$kreis[border_df$n_kreis==	334	]=	"Stadt Landau in der Pfalz"
border_df$kreis[border_df$n_kreis==	335	]=	"Stadt Ludwigshafen a. Rh."
border_df$kreis[border_df$n_kreis==	336	]=	"Stadt Mainz"
border_df$kreis[border_df$n_kreis==	337	]=	"Stadt Neustadt a.d. W."
border_df$kreis[border_df$n_kreis==	338	]=	"Stadt Pirmasens"
border_df$kreis[border_df$n_kreis==	339	]=	"Stadt Speyer"
border_df$kreis[border_df$n_kreis==	340	]=	"Stadt Trier"
border_df$kreis[border_df$n_kreis==	341	]=	"Stadt Worms"
border_df$kreis[border_df$n_kreis==	342	]=	"Stadt Zweibrücken"
border_df$kreis[border_df$n_kreis==	343	]=	"Starnberg"
border_df$kreis[border_df$n_kreis==	344	]=	"Steinburg"
border_df$kreis[border_df$n_kreis==	345	]=	"Steinfurt"
border_df$kreis[border_df$n_kreis==	346	]=	"Stendal"
border_df$kreis[border_df$n_kreis==	347	]=	"Stormarn"
border_df$kreis[border_df$n_kreis==	348	]=	"Straubing"
border_df$kreis[border_df$n_kreis==	349	]=	"Straubing-Bogen"
border_df$kreis[border_df$n_kreis==	350	]=	"Stuttgart"
border_df$kreis[border_df$n_kreis==	351	]=	"Suhl"
border_df$kreis[border_df$n_kreis==	352	]=	"Sächsische Schweiz-Osterzgebirge"
border_df$kreis[border_df$n_kreis==	353	]=	"Sömmerda"
border_df$kreis[border_df$n_kreis==	354	]=	"Südliche Weinstraße"
border_df$kreis[border_df$n_kreis==	355	]=	"Südwestpfalz"
border_df$kreis[border_df$n_kreis==	356	]=	"Teltow-Fläming"
border_df$kreis[border_df$n_kreis==	357	]=	"Tirschenreuth"
border_df$kreis[border_df$n_kreis==	358	]=	"Traunstein"
border_df$kreis[border_df$n_kreis==	359	]=	"Trier-Saarburg"
border_df$kreis[border_df$n_kreis==	360	]=	"Tuttlingen"
border_df$kreis[border_df$n_kreis==	361	]=	"Tübingen"
border_df$kreis[border_df$n_kreis==	362	]=	"Uckermark"
border_df$kreis[border_df$n_kreis==	363	]=	"Uelzen"
border_df$kreis[border_df$n_kreis==	364	]=	"Ulm"
border_df$kreis[border_df$n_kreis==	365	]=	"Unna"
border_df$kreis[border_df$n_kreis==	366	]=	"Unstrut-Hainich-Kreis"
border_df$kreis[border_df$n_kreis==	367	]=	"Unterallgäu"
border_df$kreis[border_df$n_kreis==	368	]=	"Vechta"
border_df$kreis[border_df$n_kreis==	369	]=	"Verden"
border_df$kreis[border_df$n_kreis==	370	]=	"Viersen"
border_df$kreis[border_df$n_kreis==	371	]=	"Vogelsberg"
border_df$kreis[border_df$n_kreis==	372	]=	"Vogtlandkreis"
border_df$kreis[border_df$n_kreis==	373	]=	"Vorpommern-Greifswald"
border_df$kreis[border_df$n_kreis==	374	]=	"Vorpommern-Rügen"
border_df$kreis[border_df$n_kreis==	375	]=	"Vulkaneifel"
border_df$kreis[border_df$n_kreis==	376	]=	"Waldeck-Frankenberg"
border_df$kreis[border_df$n_kreis==	377	]=	"Waldshut"
border_df$kreis[border_df$n_kreis==	378	]=	"Warendorf"
border_df$kreis[border_df$n_kreis==	379	]=	"Wartburgkreis"
border_df$kreis[border_df$n_kreis==	380	]=	"Weiden i.d.OPf."
border_df$kreis[border_df$n_kreis==	381	]=	"Weilheim-Schongau"
border_df$kreis[border_df$n_kreis==	382	]=	"Weimar"
border_df$kreis[border_df$n_kreis==	383	]=	"Weimarer Land"
border_df$kreis[border_df$n_kreis==	384	]=	"Weißenburg-Gunzenhausen"
border_df$kreis[border_df$n_kreis==	385	]=	"Werra-Meißner"
border_df$kreis[border_df$n_kreis==	386	]=	"Wesel"
border_df$kreis[border_df$n_kreis==	387	]=	"Wesermarsch"
border_df$kreis[border_df$n_kreis==	388	]=	"Westerwaldkreis"
border_df$kreis[border_df$n_kreis==	389	]=	"Wetterau"
border_df$kreis[border_df$n_kreis==	390	]=	"Wilhelmshaven"
border_df$kreis[border_df$n_kreis==	391	]=	"Wittenberg"
border_df$kreis[border_df$n_kreis==	392	]=	"Wittmund"
border_df$kreis[border_df$n_kreis==	393	]=	"Wolfenbüttel"
border_df$kreis[border_df$n_kreis==	394	]=	"Wolfsburg"
border_df$kreis[border_df$n_kreis==	395	]=	"Wunsiedel i.Fichtelgebirge"
border_df$kreis[border_df$n_kreis==	396	]=	"Wuppertal"
border_df$kreis[border_df$n_kreis==	397	]=	"Würzburg, Kreis"
border_df$kreis[border_df$n_kreis==	398	]=	"Würzburg, Stadt"
border_df$kreis[border_df$n_kreis==	399	]=	"Zollernalbkreis"
border_df$kreis[border_df$n_kreis==	400	]=	"Zwickau"

#add kreis name variable distance dataset
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05334"	]=	"Aachen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"07131"	]=	"Ahrweiler"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09771"	]=	"Aichach-Friedberg"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08425"	]=	"Alb-Donau-Kreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"16077"	]=	"Altenburger Land"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"07132"	]=	"Altenkirchen (Ww)"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"15081"	]=	"Altmarkkreis Salzwedel"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09171"	]=	"Altötting"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"07331"	]=	"Alzey-Worms"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09361"	]=	"Amberg"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09371"	]=	"Amberg-Sulzbach"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03451"	]=	"Ammerland"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"15082"	]=	"Anhalt-Bitterfeld"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09571"	]=	"Ansbach, Kreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09561"	]=	"Ansbach, Stadt"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09671"	]=	"Aschaffenburg, Kreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09661"	]=	"Aschaffenburg, Stadt"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09772"	]=	"Augsburg, Kreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09761"	]=	"Augsburg, Stadt"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03452"	]=	"Aurich"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"07332"	]=	"Bad Dürkheim"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09672"	]=	"Bad Kissingen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"07133"	]=	"Bad Kreuznach"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09173"	]=	"Bad Tölz-Wolfratshausen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08211"	]=	"Baden-Baden"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09471"	]=	"Bamberg, Kreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09461"	]=	"Bamberg, Stadt"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"12060"	]=	"Barnim"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"14625"	]=	"Bautzen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09472"	]=	"Bayreuth, Kreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09462"	]=	"Bayreuth, Stadt"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09172"	]=	"Berchtesgadener Land"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"06431"	]=	"Bergstraße"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"11000"	]=	"Berlin"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"07231"	]=	"Bernkastel-Wittlich"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08426"	]=	"Biberach"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05711"	]=	"Bielefeld"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"07134"	]=	"Birkenfeld"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05911"	]=	"Bochum"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08435"	]=	"Bodenseekreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05314"	]=	"Bonn"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05554"	]=	"Borken"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05512"	]=	"Bottrop"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"12051"	]=	"Brandenburg an der Havel"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03101"	]=	"Braunschweig"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08315"	]=	"Breisgau-Hochschwarzwald"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"04011"	]=	"Bremen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"04012"	]=	"Bremerhaven"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"15084"	]=	"Burgenlandkreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08115"	]=	"Böblingen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"15083"	]=	"Börde"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08235"	]=	"Calw"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03351"	]=	"Celle"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09372"	]=	"Cham"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"14511"	]=	"Chemnitz"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03453"	]=	"Cloppenburg"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09473"	]=	"Coburg, Kreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09463"	]=	"Coburg, Stadt"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"07135"	]=	"Cochem-Zell"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05558"	]=	"Coesfeld"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"12052"	]=	"Cottbus"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03352"	]=	"Cuxhaven"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09174"	]=	"Dachau"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"12061"	]=	"Dahme-Spreewald"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"06432"	]=	"Darmstadt-Dieburg"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09271"	]=	"Deggendorf"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03401"	]=	"Delmenhorst"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"15001"	]=	"Dessau-Roßlau"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03251"	]=	"Diepholz"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09773"	]=	"Dillingen a.d.Donau"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09279"	]=	"Dingolfing-Landau"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"01051"	]=	"Dithmarschen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09779"	]=	"Donau-Ries"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"07333"	]=	"Donnersbergkreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05913"	]=	"Dortmund"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"14612"	]=	"Dresden"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05112"	]=	"Duisburg"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05358"	]=	"Düren"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05111"	]=	"Düsseldorf"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09175"	]=	"Ebersberg"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"16061"	]=	"Eichsfeld"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09176"	]=	"Eichstätt"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"07232"	]=	"Eifelkreis Bitburg-Prüm"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"12062"	]=	"Elbe-Elster"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03402"	]=	"Emden"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08316"	]=	"Emmendingen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03454"	]=	"Emsland"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05954"	]=	"Ennepe-Ruhr-Kreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08236"	]=	"Enzkreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09177"	]=	"Erding"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"16051"	]=	"Erfurt"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09562"	]=	"Erlangen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09572"	]=	"Erlangen-Höchstadt"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"14521"	]=	"Erzgebirgskreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05113"	]=	"Essen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08116"	]=	"Esslingen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05366"	]=	"Euskirchen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"01001"	]=	"Flensburg, Stadt"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09474"	]=	"Forchheim"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"12053"	]=	"Frankfurt (Oder)"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08311"	]=	"Freiburg im Breisgau"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09178"	]=	"Freising"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08237"	]=	"Freudenstadt"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09272"	]=	"Freyung-Grafenau"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03455"	]=	"Friesland"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"06631"	]=	"Fulda"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09179"	]=	"Fürstenfeldbruck"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09573"	]=	"Fürth, Kreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09563"	]=	"Fürth, Stadt"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09180"	]=	"Garmisch-Partenkirchen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05513"	]=	"Gelsenkirchen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"16052"	]=	"Gera"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"07334"	]=	"Germersheim"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"06531"	]=	"Gießen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03151"	]=	"Gifhorn"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03153"	]=	"Goslar"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"16067"	]=	"Gotha"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03456"	]=	"Grafschaft Bentheim"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"16076"	]=	"Greiz"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"06433"	]=	"Groß-Gerau"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08117"	]=	"Göppingen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"14626"	]=	"Görlitz"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03152"	]=	"Göttingen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09774"	]=	"Günzburg"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05754"	]=	"Gütersloh"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05914"	]=	"Hagen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"15002"	]=	"Halle (Saale)"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"02000"	]=	"Hamburg"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03252"	]=	"Hameln-Pyrmont"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05915"	]=	"Hamm"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03241"	]=	"Hannover"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03353"	]=	"Harburg"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"15085"	]=	"Harz"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"12063"	]=	"Havelland"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09674"	]=	"Haßberge"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03358"	]=	"Heidekreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08221"	]=	"Heidelberg"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08135"	]=	"Heidenheim"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08125"	]=	"Heilbronn, Kreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08121"	]=	"Heilbronn, Stadt"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05370"	]=	"Heinsberg"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03154"	]=	"Helmstedt"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05758"	]=	"Herford"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05916"	]=	"Herne"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"06632"	]=	"Hersfeld-Rotenburg"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"01053"	]=	"Herzogtum Lauenburg"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"16069"	]=	"Hildburghausen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03254"	]=	"Hildesheim"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05958"	]=	"Hochsauerlandkreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"06434"	]=	"Hochtaunus"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09475"	]=	"Hof, Kreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09464"	]=  "Hof, Stadt"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08126"	]=	"Hohenlohekreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03255"	]=	"Holzminden"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05762"	]=	"Höxter"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"16070"	]=	"Ilm-Kreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09161"	]=	"Ingolstadt"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"16053"	]=	"Jena"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"15086"	]=	"Jerichower Land"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"07335"	]=	"Kaiserslautern"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08215"	]=	"Karlsruhe, Kreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08212"	]=	"Karlsruhe, Stadt"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"06633"	]=	"Kassel"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09762"	]=	"Kaufbeuren"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09273"	]=	"Kelheim"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09763"	]=	"Kempten (Allgäu)"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"01002"	]=	"Kiel, Landeshauptstadt"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09675"	]=	"Kitzingen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05154"	]=	"Kleve"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08335"	]=	"Konstanz"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05114"	]=	"Krefeld"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"06411"	]=	"Kreisfreie Stadt Darmstadt"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"06412"	]=	"Kreisfreie Stadt Frankfurt am Main"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"06611"	]=	"Kreisfreie Stadt Kassel"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"06413"	]=	"Kreisfreie Stadt Offenbach am Main"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09476"	]=	"Kronach"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09477"	]=	"Kulmbach"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"07336"	]=	"Kusel"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"16065"	]=	"Kyffhäuserkreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05315"	]=	"Köln"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"06532"	]=	"Lahn-Dill"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"06414"	]=	"Landeshauptstadt Wiesbaden"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"10042"	]=	"Landkreis Merzig-Wadern"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"10043"	]=	"Landkreis Neunkirchen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"13072"	]=	"Landkreis Rostock"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"10044"	]=	"Landkreis Saarlouis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"10046"	]=	"Landkreis St. Wendel"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09181"	]=	"Landsberg am Lech"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09274"	]=	"Landshut, Kreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09261"	]=	"Landshut, Stadt"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03457"	]=	"Leer"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"14729"	]=	"Leipzig, Kreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"14713"	]=	"Leipzig, Stadt"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05316"	]=	"Leverkusen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09478"	]=	"Lichtenfels"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"06533"	]=	"Limburg-Weilburg"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09776"	]=	"Lindau (Bodensee)"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05766"	]=	"Lippe"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08118"	]=	"Ludwigsburg"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"13076"	]=	"Ludwigslust-Parchim"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08336"	]=	"Lörrach"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"01003"	]=	"Lübeck, Hansestadt"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03354"	]=	"Lüchow-Dannenberg"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03355"	]=	"Lüneburg"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"15003"	]=	"Magdeburg"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"06435"	]=	"Main-Kinzig"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09677"	]=	"Main-Spessart"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08128"	]=	"Main-Tauber-Kreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"06436"	]=	"Main-Taunus"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"07339"	]=	"Mainz-Bingen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08222"	]=	"Mannheim"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"15087"	]=	"Mansfeld-Südharz"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"06534"	]=	"Marburg-Biedenkopf"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"07137"	]=	"Mayen-Koblenz"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"13071"	]=	"Mecklenburgische Seenplatte"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"14627"	]=	"Meißen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09764"	]=	"Memmingen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05158"	]=	"Mettmann"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09182"	]=	"Miesbach"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09676"	]=	"Miltenberg"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05770"	]=	"Minden-Lübbecke"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"14522"	]=	"Mittelsachsen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"12064"	]=	"Märkisch-Oderland"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05962"	]=	"Märkischer Kreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05116"	]=	"Mönchengladbach"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09183"	]=	"Mühldorf a.Inn"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05117"	]=	"Mülheim an der Ruhr"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09184"	]=	"München, Kreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09162"	]=	"München, Landeshauptstadt"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05515"	]=	"Münster"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08225"	]=	"Neckar-Odenwald-Kreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09775"	]=	"Neu-Ulm"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09185"	]=	"Neuburg-Schrobenhausen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09373"	]=	"Neumarkt i.d.OPf."
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"01004"	]=	"Neumünster, Stadt"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09575"	]=	"Neustadt a.d.Aisch-Bad Windsheim"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09374"	]=	"Neustadt a.d.Waldnaab"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"07138"	]=	"Neuwied"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03256"	]=	"Nienburg/Weser"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"01054"	]=	"Nordfriesland"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"16062"	]=	"Nordhausen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"14730"	]=	"Nordsachsen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"13074"	]=	"Nordwestmecklenburg"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03155"	]=	"Northeim"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09564"	]=	"Nürnberg"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09574"	]=	"Nürnberger Land"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09780"	]=	"Oberallgäu"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05374"	]=	"Oberbergischer Kreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05119"	]=	"Oberhausen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"12065"	]=	"Oberhavel"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"12066"	]=	"Oberspreewald-Lausitz"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"06437"	]=	"Odenwaldkreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"12067"	]=	"Oder-Spree"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"06438"	]=	"Offenbach"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03458"	]=	"Oldenburg, Kreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03403"	]=	"Oldenburg, Stadt"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05966"	]=	"Olpe"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08317"	]=	"Ortenaukreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03459"	]=	"Osnabrück, Kreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03404"	]=	"Osnabrück, Stadt"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08136"	]=	"Ostalbkreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09777"	]=	"Ostallgäu"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03356"	]=	"Osterholz"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"01055"	]=	"Ostholstein"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"12068"	]=	"Ostprignitz-Ruppin"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05774"	]=	"Paderborn"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09275"	]=	"Passau, Kreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09262"	]=	"Passau, Stadt"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03157"	]=	"Peine"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09186"	]=	"Pfaffenhofen a.d.Ilm"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08231"	]=	"Pforzheim"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"01056"	]=	"Pinneberg"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"01057"	]=	"Plön"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"12054"	]=	"Potsdam"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"12069"	]=	"Potsdam-Mittelmark"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"12070"	]=	"Prignitz"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08216"	]=	"Rastatt"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08436"	]=	"Ravensburg"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05562"	]=	"Recklinghausen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09276"	]=	"Regen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09375"	]=	"Regensburg, Kreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09362"	]=	"Regensburg, Stadt"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"10041"	]=	"Regionalverband Saarbrücken"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08119"	]=	"Rems-Murr-Kreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05120"	]=	"Remscheid"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"01058"	]=	"Rendsburg-Eckernförde"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08415"	]=	"Reutlingen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05362"	]=	"Rhein-Erft-Kreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"07140"	]=	"Rhein-Hunsrück-Kreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05162"	]=	"Rhein-Kreis Neuss"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"07141"	]=	"Rhein-Lahn-Kreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08226"	]=	"Rhein-Neckar-Kreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"07338"	]=	"Rhein-Pfalz-Kreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05382"	]=	"Rhein-Sieg-Kreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"06439"	]=	"Rheingau-Taunus"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05378"	]=	"Rheinisch-Bergischer Kreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09673"	]=	"Rhön-Grabfeld"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09187"	]=	"Rosenheim, Kreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09163"	]=	"Rosenheim, Stadt"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"13003"	]=	"Rostock, Hansestadt"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03357"	]=	"Rotenburg (Wümme)"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09576"	]=	"Roth"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09277"	]=	"Rottal-Inn"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08325"	]=	"Rottweil"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"16074"	]=	"Saale-Holzland-Kreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"16075"	]=	"Saale-Orla-Kreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"15088"	]=	"Saalekreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"16073"	]=	"Saalfeld-Rudolstadt"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"10045"	]=	"Saarpfalz-Kreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03102"	]=	"Salzgitter"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"15089"	]=	"Salzlandkreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03257"	]=	"Schaumburg"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"01059"	]=	"Schleswig-Flensburg"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"16066"	]=	"Schmalkalden-Meiningen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09565"	]=	"Schwabach"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"06634"	]=	"Schwalm-Eder"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09376"	]=	"Schwandorf"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08326"	]=	"Schwarzwald-Baar-Kreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09678"	]=	"Schweinfurt, Kreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09662"	]=	"Schweinfurt, Stadt"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"13004"	]=	"Schwerin, Landeshauptstadt"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08127"	]=	"Schwäbisch Hall"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"01060"	]=	"Segeberg"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05970"	]=	"Siegen-Wittgenstein"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08437"	]=	"Sigmaringen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05974"	]=	"Soest"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05122"	]=	"Solingen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"16072"	]=	"Sonneberg"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"12071"	]=	"Spree-Neiße"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03359"	]=	"Stade"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"07311"	]=	"Stadt Frankenthal (Pfalz)"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"07312"	]=	"Stadt Kaiserslautern"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"07111"	]=	"Stadt Koblenz"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"07313"	]=	"Stadt Landau in der Pfalz"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"07314"	]=	"Stadt Ludwigshafen a. Rh."
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"07315"	]=	"Stadt Mainz"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"07316"	]=	"Stadt Neustadt a.d. W."
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"07317"	]=	"Stadt Pirmasens"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"07318"	]=	"Stadt Speyer"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"07211"	]=	"Stadt Trier"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"07319"	]=	"Stadt Worms"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"07320"	]=	"Stadt Zweibrücken"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09188"	]=	"Starnberg"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"01061"	]=	"Steinburg"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05566"	]=	"Steinfurt"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"15090"	]=	"Stendal"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"01062"	]=	"Stormarn"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09263"	]=	"Straubing"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09278"	]=	"Straubing-Bogen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08111"	]=	"Stuttgart"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"16054"	]=	"Suhl"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"14628"	]=	"Sächsische Schweiz-Osterzgebirge"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"16068"	]=	"Sömmerda"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"07337"	]=	"Südliche Weinstraße"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"07340"	]=	"Südwestpfalz"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"12072"	]=	"Teltow-Fläming"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09377"	]=	"Tirschenreuth"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09189"	]=	"Traunstein"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"07235"	]=	"Trier-Saarburg"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08327"	]=	"Tuttlingen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08416"	]=	"Tübingen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"12073"	]=	"Uckermark"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03360"	]=	"Uelzen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08421"	]=	"Ulm"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05978"	]=	"Unna"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"16064"	]=	"Unstrut-Hainich-Kreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09778"	]=	"Unterallgäu"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03460"	]=	"Vechta"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03361"	]=	"Verden"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05166"	]=	"Viersen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"06535"	]=	"Vogelsberg"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"14523"	]=	"Vogtlandkreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"13075"	]=	"Vorpommern-Greifswald"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"13073"	]=	"Vorpommern-Rügen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"07233"	]=	"Vulkaneifel"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"06635"	]=	"Waldeck-Frankenberg"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08337"	]=	"Waldshut"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05570"	]=	"Warendorf"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"16063"	]=	"Wartburgkreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09363"	]=	"Weiden i.d.OPf."
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09190"	]=	"Weilheim-Schongau"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"16055"	]=	"Weimar"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"16071"	]=	"Weimarer Land"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09577"	]=	"Weißenburg-Gunzenhausen"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"06636"	]=	"Werra-Meißner"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05170"	]=	"Wesel"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03461"	]=	"Wesermarsch"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"07143"	]=	"Westerwaldkreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"06440"	]=	"Wetterau"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03405"	]=	"Wilhelmshaven"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"15091"	]=	"Wittenberg"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03462"	]=	"Wittmund"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03158"	]=	"Wolfenbüttel"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"03103"	]=	"Wolfsburg"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09479"	]=	"Wunsiedel i.Fichtelgebirge"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"05124"	]=	"Wuppertal"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09679"	]=	"Würzburg, Kreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"09663"	]=	"Würzburg, Stadt"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"08417"	]=	"Zollernalbkreis"
Kreise_distance_to_borderGEN$kreis[Kreise_distance_to_borderGEN$RS==	"14524"	]=	"Zwickau"

Kreise_distance_to_borderGEN = Kreise_distance_to_borderGEN %>%
  drop_na(kreis)

Kreise_distance_to_borderGEN = Kreise_distance_to_borderGEN %>% select(kreis,dist,closest_country)

# merge border dataset to distances
border_df_dist <- list(border_df, Kreise_distance_to_borderGEN) %>%
  reduce(left_join, by=c("kreis" = "kreis"))

border_df_dist <- dplyr::rename(border_df_dist, 
                                dist_old=dist.x,
                                dist_kreis=dist.y)
#DROP NOT NEEDED VARIABLES
borders_red <- border_df_dist  %>%
  select(date, n_kreis,iso2_border,iso2, dist_kreis,closest_country, sea_dist_25,
         country_code_distance25, code_m18_030, kreis, bundesland, country_code_kreis,kr_inz_rate, kr_neuinf_rate, 
         oxf_closure_ger, oxf_closure_den,oxf_closure_pol, oxf_closure_cze, oxf_closure_aus, oxf_closure_aus, 
         oxf_closure_swi, oxf_closure_fra, oxf_closure_lux, oxf_closure_bel, oxf_closure_ned, 135:154)

#1.1 merge each with borders dataset
master_06 <- list(reg_master_06, borders_red) %>%
  reduce(left_join, by=c("kreis_name" = "kreis", "date"="date"))
master_07 <- list(reg_master_07, borders_red) %>%
  reduce(left_join, by=c("kreis_name" = "kreis", "date"="date"))
master_08 <- list(reg_master_08, borders_red) %>%
  reduce(left_join, by=c("kreis_name" = "kreis", "date"="date"))
master_09 <- list(reg_master_09, borders_red) %>%
  reduce(left_join, by=c("kreis_name" = "kreis", "date"="date"))
master_10 <- list(reg_master_10, borders_red) %>%
  reduce(left_join, by=c("kreis_name" = "kreis", "date"="date"))
master_11 <- list(reg_master_11, borders_red) %>%
  reduce(left_join, by=c("kreis_name" = "kreis", "date"="date"))
master_12 <- list(reg_master_12, borders_red) %>%
  reduce(left_join, by=c("kreis_name" = "kreis", "date"="date"))
master_13 <- list(reg_master_13, borders_red) %>%
  reduce(left_join, by=c("kreis_name" = "kreis", "date"="date"))
master_14 <- list(reg_master_14, borders_red) %>%
  reduce(left_join, by=c("kreis_name" = "kreis", "date"="date"))
master_15 <- list(reg_master_15, borders_red) %>%
  reduce(left_join, by=c("kreis_name" = "kreis", "date"="date"))
master_16 <- list(reg_master_16, borders_red) %>%
  reduce(left_join, by=c("kreis_name" = "kreis", "date"="date"))
master_17 <- list(reg_master_17, borders_red) %>%
  reduce(left_join, by=c("kreis_name" = "kreis", "date"="date"))
master_18 <- list(reg_master_18, borders_red) %>%
  reduce(left_join, by=c("kreis_name" = "kreis", "date"="date"))
master_19 <- list(reg_master_19, borders_red) %>%
  reduce(left_join, by=c("kreis_name" = "kreis", "date"="date"))
master_20 <- list(reg_master_20, borders_red) %>%
  reduce(left_join, by=c("kreis_name" = "kreis", "date"="date"))

#distamnes extreme (based on descriptive)
table(border_df_dist$dist_kreis)

master_20$dist_extremes=NA
master_20$dist_extremes[master_20$dist_kreis<=28.1615]=1
master_20$dist_extremes[master_20$dist_kreis>106.1921]=0

master_19$dist_extremes=NA
master_19$dist_extremes[master_19$dist_kreis<=28.1615]=1
master_19$dist_extremes[master_19$dist_kreis>106.1921]=0

master_18$dist_extremes=NA
master_18$dist_extremes[master_18$dist_kreis<=28.1615]=1
master_18$dist_extremes[master_18$dist_kreis>106.1921]=0

master_17$dist_extremes=NA
master_17$dist_extremes[master_17$dist_kreis<=28.1615]=1
master_17$dist_extremes[master_17$dist_kreis>106.1921]=0

master_16$dist_extremes=NA
master_17$dist_extremes[master_17$dist_kreis<=28.1615]=1
master_17$dist_extremes[master_17$dist_kreis>106.1921]=0

master_15$dist_extremes=NA
master_15$dist_extremes[master_15$dist_kreis<=28.1615]=1
master_15$dist_extremes[master_15$dist_kreis>106.1921]=0

master_14$dist_extremes=NA
master_14$dist_extremes[master_14$dist_kreis<=28.1615]=1
master_14$dist_extremes[master_14$dist_kreis>106.1921]=0

master_13$dist_extremes=NA
master_13$dist_extremes[master_13$dist_kreis<=28.1615]=1
master_13$dist_extremes[master_13$dist_kreis>106.1921]=0

master_12$dist_extremes=NA
master_12$dist_extremes[master_12$dist_kreis<=28.1615]=1
master_12$dist_extremes[master_12$dist_kreis>106.1921]=0

master_11$dist_extremes=NA
master_11$dist_extremes[master_11$dist_kreis<=28.1615]=1
master_11$dist_extremes[master_11$dist_kreis>106.1921]=0

master_10$dist_extremes=NA
master_10$dist_extremes[master_10$dist_kreis<=28.1615]=1
master_10$dist_extremes[master_10$dist_kreis>106.1921]=0

master_09$dist_extremes=NA
master_09$dist_extremes[master_09$dist_kreis<=28.1615]=1
master_09$dist_extremes[master_09$dist_kreis>106.1921]=0

master_08$dist_extremes=NA
master_08$dist_extremes[master_08$dist_kreis<=28.1615]=1
master_08$dist_extremes[master_08$dist_kreis>106.1921]=0

master_07$dist_extremes=NA
master_07$dist_extremes[master_07$dist_kreis<=28.1615]=1
master_07$dist_extremes[master_07$dist_kreis>106.1921]=0

master_06$dist_extremes=NA
master_06$dist_extremes[master_06$dist_kreis<=28.1615]=1
master_06$dist_extremes[master_06$dist_kreis>106.1921]=0

###dist 25
master_20$dist_25=NA
master_20$dist_25[master_20$dist_kreis<=25]=1
master_20$dist_25[master_20$dist_kreis>25]=0

master_19$dist_25=NA
master_19$dist_25[master_19$dist_kreis<=25]=1
master_19$dist_25[master_19$dist_kreis>25]=0

master_18$dist_25=NA
master_18$dist_25[master_18$dist_kreis<=25]=1
master_18$dist_25[master_18$dist_kreis>25]=0

master_17$dist_25=NA
master_17$dist_25[master_17$dist_kreis<=25]=1
master_17$dist_25[master_17$dist_kreis>25]=0

master_16$dist_25=NA
master_17$dist_25[master_17$dist_kreis<=25]=1
master_17$dist_25[master_17$dist_kreis>25]=0

master_15$dist_25=NA
master_15$dist_25[master_15$dist_kreis<=25]=1
master_15$dist_25[master_15$dist_kreis>25]=0

master_14$dist_25=NA
master_14$dist_25[master_14$dist_kreis<=25]=1
master_14$dist_25[master_14$dist_kreis>25]=0

master_13$dist_25=NA
master_13$dist_25[master_13$dist_kreis<=25]=1
master_13$dist_25[master_13$dist_kreis>25]=0

master_12$dist_25=NA
master_12$dist_25[master_12$dist_kreis<=25]=1
master_12$dist_25[master_12$dist_kreis>25]=0

master_11$dist_25=NA
master_11$dist_25[master_11$dist_kreis<=25]=1
master_11$dist_25[master_11$dist_kreis>25]=0

master_10$dist_25=NA
master_10$dist_25[master_10$dist_kreis<=25]=1
master_10$dist_25[master_10$dist_kreis>25]=0

master_09$dist_25=NA
master_09$dist_25[master_09$dist_kreis<=25]=1
master_09$dist_25[master_09$dist_kreis>25]=0

master_08$dist_25=NA
master_08$dist_25[master_08$dist_kreis<=25]=1
master_08$dist_25[master_08$dist_kreis>25]=0

master_07$dist_25=NA
master_07$dist_25[master_07$dist_kreis<=25]=1
master_07$dist_25[master_07$dist_kreis>25]=0

master_06$dist_25=NA
master_06$dist_25[master_06$dist_kreis<=25]=1
master_06$dist_25[master_06$dist_kreis>25]=0

###dist 50
master_20$dist_50=NA
master_20$dist_50[master_20$dist_kreis<=50]=1
master_20$dist_50[master_20$dist_kreis>50]=0

master_19$dist_50=NA
master_19$dist_50[master_19$dist_kreis<=50]=1
master_19$dist_50[master_19$dist_kreis>50]=0

master_18$dist_50=NA
master_18$dist_50[master_18$dist_kreis<=50]=1
master_18$dist_50[master_18$dist_kreis>50]=0

master_17$dist_50=NA
master_17$dist_50[master_17$dist_kreis<=50]=1
master_17$dist_50[master_17$dist_kreis>50]=0

master_16$dist_50=NA
master_17$dist_50[master_17$dist_kreis<=50]=1
master_17$dist_50[master_17$dist_kreis>50]=0

master_15$dist_50=NA
master_15$dist_50[master_15$dist_kreis<=50]=1
master_15$dist_50[master_15$dist_kreis>50]=0

master_14$dist_50=NA
master_14$dist_50[master_14$dist_kreis<=50]=1
master_14$dist_50[master_14$dist_kreis>50]=0

master_13$dist_50=NA
master_13$dist_50[master_13$dist_kreis<=50]=1
master_13$dist_50[master_13$dist_kreis>50]=0

master_12$dist_50=NA
master_12$dist_50[master_12$dist_kreis<=50]=1
master_12$dist_50[master_12$dist_kreis>50]=0

master_11$dist_50=NA
master_11$dist_50[master_11$dist_kreis<=50]=1
master_11$dist_50[master_11$dist_kreis>50]=0

master_10$dist_50=NA
master_10$dist_50[master_10$dist_kreis<=50]=1
master_10$dist_50[master_10$dist_kreis>50]=0

master_09$dist_50=NA
master_09$dist_50[master_09$dist_kreis<=50]=1
master_09$dist_50[master_09$dist_kreis>50]=0

master_08$dist_50=NA
master_08$dist_50[master_08$dist_kreis<=50]=1
master_08$dist_50[master_08$dist_kreis>50]=0

master_07$dist_50=NA
master_07$dist_50[master_07$dist_kreis<=50]=1
master_07$dist_50[master_07$dist_kreis>50]=0

master_06$dist_50=NA
master_06$dist_50[master_06$dist_kreis<=50]=1
master_06$dist_50[master_06$dist_kreis>50]=0

#regmaster20<- reg_master_20 %>% filter(date < as.Date("2021-01-01"))

master_20$border_region_25=0
master_20$border_region_25[master_20$dist_25==1]=1

master_19$border_region_25=0
master_19$border_region_25[master_20$dist_25==1]=1

master_18$border_region_25=0
master_18$border_region_25[master_20$dist_25==1]=1

master_17$border_region_25=0
master_17$border_region_25[master_20$dist_25==1]=1

master_16$border_region_25=0
master_16$border_region_25[master_20$dist_25==1]=1

master_15$border_region_25=0
master_15$border_region_25[master_20$dist_25==1]=1

master_14$border_region_25=0
master_14$border_region_25[master_20$dist_25==1]=1

master_13$border_region_25=0
master_13$border_region_25[master_20$dist_25==1]=1

master_12$border_region_25=0
master_12$border_region_25[master_20$dist_25==1]=1

master_11$border_region_25=0
master_11$border_region_25[master_20$dist_25==1]=1

master_10$border_region_25=0
master_10$border_region_25[master_20$dist_25==1]=1

master_09$border_region_25=0
master_09$border_region_25[master_20$dist_25==1]=1

master_08$border_region_25=0
master_08$border_region_25[master_20$dist_25==1]=1

master_07$border_region_25=0
master_07$border_region_25[master_20$dist_25==1]=1

master_06$border_region_25=0
master_06$border_region_25[master_20$dist_25==1]=1

#50km
master_20$border_region_50=0
master_20$border_region_50[master_20$dist_50==1]=1

master_19$border_region_50=0
master_19$border_region_50[master_20$dist_50==1]=1

master_18$border_region_50=0
master_18$border_region_50[master_20$dist_50==1]=1

master_17$border_region_50=0
master_17$border_region_50[master_20$dist_50==1]=1

master_16$border_region_50=0
master_16$border_region_50[master_20$dist_50==1]=1

master_15$border_region_50=0
master_15$border_region_50[master_20$dist_50==1]=1

master_14$border_region_50=0
master_14$border_region_50[master_20$dist_50==1]=1

master_13$border_region_50=0
master_13$border_region_50[master_20$dist_50==1]=1

master_12$border_region_50=0
master_12$border_region_50[master_20$dist_50==1]=1

master_11$border_region_50=0
master_11$border_region_50[master_20$dist_50==1]=1

master_10$border_region_50=0
master_10$border_region_50[master_20$dist_50==1]=1

master_09$border_region_50=0
master_09$border_region_50[master_20$dist_50==1]=1

master_08$border_region_50=0
master_08$border_region_50[master_20$dist_50==1]=1

master_07$border_region_50=0
master_07$border_region_50[master_20$dist_50==1]=1

master_06$border_region_50=0
master_06$border_region_50[master_20$dist_50==1]=1

df_long <- dplyr::bind_rows(master_20, master_19, master_18,master_17, master_16, master_15,master_14,
                            master_13, master_12,master_11, master_10, master_09,master_08, master_07, master_06)

saveRDS(df_long,"~/work/November_2024/R_out/df_long.rds")

source_year =2020
#iso's only exist in 2020! it is missing in other years, 2020 iso s need to be assigned to each year. the below code does that
df_long = df_long %>%
  mutate(iso2 = ifelse(is.na(iso2), iso2[year==source_year], iso2))

# then pop var is from 2017. so we need to get it like this
df_filled_small = df_long %>%
  filter(year==2017) %>%
  select(iso2, kr_population, kreis_name)

# this dataset is repetitive, remove duplicates
df_filled_small_unique = distinct(df_filled_small, kreis_name, .keep_all = TRUE )

# remove where iso2 is missing
df_filled_small_unique = df_filled_small_unique %>%
  filter(!is.na(iso2))

# now we have all data we want side by side. but now we need to aggregate by iso2. 
df_filled_small_unique$iso2 = as.factor(df_filled_small_unique$iso2)

df_filled_small_unique_agg = aggregate(kr_population ~ iso2, data = df_filled_small_unique, FUN = sum, na.rm=TRUE)

#DONE.

colnames(df_filled_small_unique_agg)[2] = "iso2_pop"

df_filled_small_unique_agg$iso2 = as.numeric(df_filled_small_unique_agg$iso2)

###########################################################################################################################
#### Remove NAs from long_df
#df_long = readRDS("~/work/2023-02-24/R_out/df_long.rds")
df_long[df_long==-8]=NA
df_long[df_long==-7]=NA
df_long[df_long==-6]=NA
df_long[df_long==-5]=NA
df_long[df_long==-4]=NA
df_long[df_long==-3]=NA
df_long[df_long==-2]=NA
df_long[df_long==-1]=NA
########CREATE DUMMY VARIABLES
#Party Preferences
df_long$CDU_CSU=NA
df_long$CDU_CSU[df_long$polparty!=2]=0
df_long$CDU_CSU[df_long$polparty!=3]=0
df_long$CDU_CSU[df_long$polparty==2]=1
df_long$CDU_CSU[df_long$polparty==3]=1

df_long$SPD=NA
df_long$SPD[df_long$polparty!=1]=0
df_long$SPD[df_long$polparty==1]=1

df_long$FDP=NA
df_long$FDP[df_long$polparty!=4]=0
df_long$FDP[df_long$polparty==4]=1

df_long$GRUNE=NA
df_long$GRUNE[df_long$polparty!=5]=0
df_long$GRUNE[df_long$polparty==5]=1
summary(df_long$GRUNE)

df_long$LINKE=NA
df_long$LINKE[df_long$polparty!=6]=0
df_long$LINKE[df_long$polparty==6]=1
summary(df_long$LINKE)

df_long$AfD=NA
df_long$AfD[df_long$polparty!=27]=0
df_long$AfD[df_long$polparty==27]=1

df_long$OTHER_PARTY=NA
df_long$OTHER_PARTY[df_long$polparty!=8]=0
df_long$OTHER_PARTY[df_long$polparty==8]=1

#Bundestagswahl 2017 - Voting

df_long$CDU_2018=NA
df_long$CDU_2018[df_long$voting_2018==29]= NA
df_long$CDU_2018[df_long$voting_2018!=2]=0
df_long$CDU_2018[df_long$voting_2018==2]=1
summary(df_long$CDU_2018)

df_long$CSU_2018=NA
df_long$CSU_2018[df_long$voting_2018==29]= NA
df_long$CSU_2018[df_long$voting_2018==3]=1
df_long$CSU_2018[df_long$voting_2018==3]=1

df_long$SPD_2018=NA
df_long$SPD_2018[df_long$voting_2018==29]= NA
df_long$SPD_2018[df_long$voting_2018==1]=1
df_long$SPD_2018[df_long$voting_2018==1]=1

df_long$FDP_2018=NA
df_long$FDP_2018[df_long$voting_2018==29]= NA
df_long$FDP_2018[df_long$voting_2018==4]=1
df_long$FDP_2018[df_long$voting_2018==4]=1

df_long$GRUNE_2018=NA
df_long$GRUNE_2018[df_long$voting_2018==29]= NA
df_long$GRUNE_2018[df_long$voting_2018==5]=1
df_long$GRUNE_2018[df_long$voting_2018==5]=1

df_long$LINKE_2018=NA
df_long$LINKE_2018[df_long$voting_2018==29]= NA
df_long$LINKE_2018[df_long$voting_2018==6]=1
df_long$LINKE_2018[df_long$voting_2018==6]=1

df_long$AfD_2018=NA
df_long$AfD_2018[df_long$voting_2018==29]= NA
df_long$AfD_2018[df_long$voting_2018==27]=1
df_long$AfD_2018[df_long$voting_2018==27]=1

df_long$NPD_REPUB_RECHTE_2018=NA
df_long$NPD_REPUB_RECHTE_2018[df_long$voting_2018==29]= NA
df_long$NPD_REPUB_RECHTE_2018[df_long$voting_2018==7]=1
df_long$NPD_REPUB_RECHTE_2018[df_long$voting_2018==7]=1

df_long$OTHER_PARTY_2018=NA
df_long$OTHER_PARTY_2018[df_long$voting_2018==29]= NA
df_long$OTHER_PARTY_2018[df_long$voting_2018==8]=1
df_long$OTHER_PARTY_2018[df_long$voting_2018==8]=1

df_long$NOVOTE_2018=NA
df_long$NOVOTE_2018[df_long$voting_2018==29]= NA
df_long$NOVOTE_2018[df_long$voting_2018==28]=1
df_long$NOVOTE_2018[df_long$voting_2018==28]=1

df_long$NOELEGIBILITYVOTE_2018=NA
df_long$NOELEGIBILITYVOTE_2018[df_long$voting_2018==29]=1
df_long$NOELEGIBILITYVOTE_2018[df_long$voting_2018==29]=1

#Recoding based on Chapel Hill Expert Survey - Left-right ideology
df_long$Voting_2018_LeftRight=NA
df_long$Voting_2018_LeftRight[df_long$voting_2018==6]=1 #Linke
df_long$Voting_2018_LeftRight[df_long$voting_2018==5]=2 #Gruene
df_long$Voting_2018_LeftRight[df_long$voting_2018==1]=3 #SPD
df_long$Voting_2018_LeftRight[df_long$voting_2018==2]=4 #CDU
df_long$Voting_2018_LeftRight[df_long$voting_2018==4]=5 #FDP
df_long$Voting_2018_LeftRight[df_long$voting_2018==3]=6 #CSU
df_long$Voting_2018_LeftRight[df_long$voting_2018==27]=7 #AFD
summary(df_long$Voting_2018_LeftRight)

#Recoding based on Chapel Hill Expert Survey - GAL-TAN
df_long$Voting_2018_GALTAN=NA
df_long$Voting_2018_GALTAN[df_long$voting_2018==5]=1 #Gruene
df_long$Voting_2018_GALTAN[df_long$voting_2018==6]=2 #Linke
df_long$Voting_2018_GALTAN[df_long$voting_2018==1]=3 #SPD
df_long$Voting_2018_GALTAN[df_long$voting_2018==4]=4 #FDP
df_long$Voting_2018_GALTAN[df_long$voting_2018==2]=5 #CDU
df_long$Voting_2018_GALTAN[df_long$voting_2018==3]=6 #CSU
df_long$Voting_2018_GALTAN[df_long$voting_2018==27]=7 #AFD
summary(df_long$Voting_2018_GALTAN)

#Option to work from home
df_long$HOMEOFFICE=NA
df_long$HOMEOFFICE[df_long$work_home==2]=0
df_long$HOMEOFFICE[df_long$work_home==1 ]=1
table(df_long$HOMEOFFICE)

#Contacts abroad (2019 - based)
df_long$CONTACTABROAD=NA
df_long$CONTACTABROAD[df_long$contact_friends==1]=1
df_long$CONTACTABROAD[df_long$contact_friends==2]=0
summary(df_long$CONTACTABROAD)

df_long$german_citizenship<-as.factor(df_long$german_citizenship)
df_long$second_citizenship<-as.factor(df_long$second_citizenship)

# Bring in distances from Moran import
df_distance <- read_csv("~/transfer/import/fromMoran/2022-10-12/lherbig-soep_v37_hh_export-df_distances_anon.csv")

df_distance <- dplyr::rename(df_distance, 
                             dist_ind=dist)
df_distance = df_distance %>%
  filter(syear > 2005)

df_distance_2020 = df_distance %>%
  filter(syear > 2019)

df_distance_2020 = df_distance_2020 %>%
  select(hid, dist_ind)

df_distance_2020$hid = unique(df_distance_2020$hid)

df_long <- left_join(df_long, df_distance_2020, by=c("hid"="hid"))

##border region dummy individual level
###dist extremes
df_long$dist_extremes_ind=NA
df_long$dist_extremes_ind[df_long$dist_ind<=35.03]=1
df_long$dist_extremes_ind[df_long$dist_ind>102.20]=0

###dist 25
df_long$dist_25_ind=NA
df_long$dist_25_ind[df_long$dist_ind<=25]=1
df_long$dist_25_ind[df_long$dist_ind>25]=0

###dist 50
df_long$dist_50_ind=NA
df_long$dist_50_ind[df_long$dist_ind<=50]=1
df_long$dist_50_ind[df_long$dist_ind>50]=0

#border region dummy 25
df_long$border_region_ind_25=0
df_long$border_region_ind_25[df_long$dist_25_ind==1]=1

#border region dummy 50
df_long$border_region_ind_50=0
df_long$border_region_ind_50[df_long$dist_50_ind==1]=1

####assign iso2 to all years
df_long_2020 = df_long %>%
  filter(syear > 2019)

df_long_2020 = df_long_2020 %>%
  select(pid, iso2)

df_long_2020$pid = unique(df_long_2020$pid)

df_long <- left_join(df_long, df_long_2020, by=c("pid"="pid"))

###assign 0 for infection rates before 2020
df_long$kr_inz_rate[df_long$kr_inz_rate ==	-99	]=	NA
df_long$kr_inz_rate[df_long$syear<=	2019	]=	0

df_long$kr_neuinf_rate[df_long$kr_neuinf_rate == -99	]=	NA
df_long$kr_neuinf_rate[df_long$syear<=	2019	]=	0

###########################################################PCA WORRY DF_LONG
matrix_worry <- as.matrix(df_long[,c("worry_crime", "worry_eco_dev", "worry_eco_sit", "worry_ret_prov", "worry_health", "worry_mig_host",
                                     "worry_tech", "worry_quali", "worry_env_pro", "worry_env_change", "worry_peace", "worry_crime",
                                     "worry_soc_coh", "worry_migration", "worry_sec_work", "worry_wlb")])

#Remove any rows with missing values
matrix_worry <- matrix_worry[complete.cases(matrix_worry),]

#Perform PCA on the cleaned matrix
pca_result_worry <- prcomp(matrix_worry, center=TRUE, scale.=TRUE)

#Create new worry_pca variable
df_long$worry_pca <- NA
non_missing_rows <- which(complete.cases(df_long[,c("worry_crime", "worry_eco_dev", "worry_eco_sit", "worry_ret_prov", "worry_health", "worry_mig_host",
                                                    "worry_tech", "worry_quali", "worry_env_pro", "worry_env_change", "worry_peace", "worry_crime",
                                                    "worry_soc_coh", "worry_migration", "worry_sec_work", "worry_wlb")]))
df_long$worry_pca[non_missing_rows] = pca_result_worry$x[,1]



###########################################################PCA IMMIGRATION DF_LONG

normalize = function(x) {
  data_range = max(x, na.rm = TRUE) - min(x, na.rm = TRUE)
  return((x - min(x, na.rm = TRUE)) / data_range)
}

matrix_immigration <- as.matrix(df_long[,c("refugee_eco", "refugee_culture", "refugee_ger_living")])

#Remove any rows with missing values
matrix_immigration <- matrix_immigration[complete.cases(matrix_immigration),]

#Perform PCA on the cleaned matrix
pca_result_immigration <- prcomp(matrix_immigration, scale.=TRUE)

#Create new worry_pca variable
df_long$immigration_pca <- NA
non_missing_rows <- which(complete.cases(df_long[,c("refugee_eco", "refugee_culture", "refugee_ger_living")]))
df_long$immigration_pca[non_missing_rows] = pca_result_immigration$x[,1]

summary(df_long$refugee_eco)
summary(df_long$refugee_culture)
summary(df_long$refugee_ger_living)


df_long_ind <- df_long

###########################################################PCA WORRY DF_LONG_INDIVIDUAL
matrix_worry <- as.matrix(df_long_ind[,c("worry_crime", "worry_eco_dev", "worry_eco_sit", "worry_ret_prov", "worry_health", "worry_mig_host",
                                         "worry_tech", "worry_quali", "worry_env_pro", "worry_env_change", "worry_peace", "worry_crime",
                                         "worry_soc_coh", "worry_migration", "worry_sec_work", "worry_wlb")])

#Remove any rows with missing values
matrix_worry <- matrix_worry[complete.cases(matrix_worry),]

#Perform PCA on the cleaned matrix
pca_result_worry <- prcomp(matrix_worry, center=TRUE, scale.=TRUE)

#Create new worry_pca variable
df_long_ind$worry_pca <- NA
non_missing_rows <- which(complete.cases(df_long_ind[,c("worry_crime", "worry_eco_dev", "worry_eco_sit", "worry_ret_prov", "worry_health", "worry_mig_host",
                                                        "worry_tech", "worry_quali", "worry_env_pro", "worry_env_change", "worry_peace", "worry_crime",
                                                        "worry_soc_coh", "worry_migration", "worry_sec_work", "worry_wlb")]))
df_long_ind$worry_pca[non_missing_rows] = pca_result_worry$x[,1]



###########################################################PCA IMMIGRATION DF_LONG_INDIVIDUAL
matrix_immigration <- as.matrix(df_long_ind[,c("refugee_eco", "refugee_culture", "refugee_ger_living")])

#Remove any rows with missing values
matrix_immigration <- matrix_immigration[complete.cases(matrix_immigration),]

#Perform PCA on the cleaned matrix
pca_result_immigration <- prcomp(matrix_immigration, center=TRUE, scale.=TRUE)

#Create new worry_pca variable
df_long_ind$immigration_pca <- NA
non_missing_rows <- which(complete.cases(df_long_ind[,c("refugee_eco", "refugee_culture", "refugee_ger_living")]))
df_long_ind$immigration_pca[non_missing_rows] = pca_result_immigration$x[,1]



saveRDS(df_long_ind,"~/work/November_2024/R_out/df_long_ind.rds")

###### now create 2020 Kreis-Date level dataset
# Aggregate by week
###### now create Kreis-Weekyear dataset

df_long$weekyear <- format(as.Date(df_long$date), "%Y-%W")
df_long$monthyear <- format(as.Date(df_long$date), "%Y-%m")

df_long$twoweek = ((week(df_long$date) - 1) %/% 2)+1
df_long$twoweekyear = paste0(df_long$year, "-", df_long$twoweek)
print(df_long$twoweekyear)

df_long %>%
  select(date, weekyear, twoweekyear, monthyear)

# take mean of relevant variables
df_long$age = df_long$year - df_long$birthyear

df_long$Voting_2018_GALTAN
#################### AGGREGATE BY iso2.y AND WEEK
library(dplyr)
df_mean_iso <- df_long %>%
  group_by(weekyear, iso2.y) %>%
  summarise_at(vars(7:10, 15:17, 19:34, 37:42, 59:61, 63, 76:78, 81, 83:84, 96:97, 151:152, 155, worry_pca, immigration_pca, age, syear), mean, na.rm=TRUE)

# take agg of relevant variables
library(haven)
df_long$no_survey_translation = zap_labels(df_long$no_survey_translation)
df_long$no_survey_translation = as.numeric(df_long$no_survey_translation)

df_long$translation=NA
df_long$translation[df_long$no_survey_translation==1]=1

df_long$mode_self_complete=NA
df_long$mode_self_complete[df_long$interview_completion==3]=1

df_long$mode_phone=NA
df_long$mode_phone[df_long$interview_completion==0]=1

df_long$mode_verbal=NA
df_long$mode_verbal[df_long$interview_completion==1]=1

df_long$mode_written=NA
df_long$mode_written[df_long$year>2019]=0
df_long$mode_written[df_long$interview_completion==8]=1

df_long$mode_capi=NA
df_long$mode_capi[df_long$interview_completion==9]=1

df_long$mode_cawi=NA
df_long$mode_cawi[df_long$interview_completion==10]=1

df_long$full_time_employed=NA
df_long$full_time_employed[df_long$work_status==10]=1

df_long$part_time_employed=NA
df_long$part_time_employed[df_long$work_status==2]=1

df_long$ausbildung=NA
df_long$ausbildung[df_long$work_status==3]=1

df_long$marginally_employed=NA
df_long$marginally_employed[df_long$work_status==4]=1

df_long$unemployed=NA
df_long$unemployed[df_long$work_status==9]=1

df_long$self_employed=NA
df_long$self_employed[df_long$occupational_status==1]=1

df_long$worker=NA
df_long$worker[df_long$occupational_status==2]=1

df_long$civil_servant=NA
df_long$civil_servant[df_long$occupational_status==3]=1

df_long$trainee=NA
df_long$trainee[df_long$occupational_status==4]=1

df_long$employee=NA
df_long$employee[df_long$occupational_status==5]=1

df_long$changing_region=NA
df_long$changing_region[df_long$work_abroad==1]=1

df_long$working_abroad=NA
df_long$working_abroad[df_long$work_abroad==2]=1

df_long$high_school=NA
df_long$high_school[df_long$current_education==1]=1

df_long$university=NA
df_long$university[df_long$current_education==2]=1

df_long$vocational=NA
df_long$vocational[df_long$current_education==3]=1

df_long$continued_education=NA
df_long$continued_education[df_long$current_education==4]=1

df_long$no_doctor_3month = zap_labels(df_long$no_doctor_3month)
df_long$no_doctor_3month = as.numeric(df_long$no_doctor_3month)

df_long$one_party_cat=NA
df_long$one_party_cat[df_long$one_party==1]=1

df_long$german_citizen_cat=NA
df_long$german_citizen_cat[df_long$german_citizenship==1]=1

df_long$second_citizen_cat=NA
df_long$second_citizen_cat[df_long$second_citizenship==1]=1

df_long$other_residence=NA
df_long$other_residence[df_long$res_stat==1]=1
df_long$other_residence[df_long$res_stat==2]=1
df_long$other_residence[df_long$res_stat==3]=1
df_long$other_residence[df_long$res_stat==4]=1
df_long$other_residence[df_long$res_stat==5]=1
df_long$other_residence[df_long$res_stat==7]=1

df_long$EU_residence=NA
df_long$EU_residence[df_long$res_stat==1]=1

df_long$female=NA
df_long$female[df_long$sex==2]=1

df_long$treat21_any_lib_corecontrol=0
df_long$treat21_any_lib_corecontrol[df_long$treat5_any_CL_OXlib==1 & df_long$border_region==1]=1

df_long$treat22_any_str_corecontrol=0
df_long$treat22_any_str_corecontrol[df_long$treat6_any_CL_OXstr==1 & df_long$border_region==1]=1

############# Summarize (SUM) variables BY ISO AND WEEK
library(dplyr)
df_agg_iso <- df_long %>%
  group_by(weekyear, iso2.y) %>%
  summarise_at(vars(translation, mode_self_complete, mode_phone, mode_verbal, mode_written, mode_cawi, mode_capi,
                    full_time_employed, part_time_employed, ausbildung, marginally_employed, unemployed,
                    self_employed, worker, civil_servant, trainee, employee,
                    changing_region, working_abroad, high_school, university, 
                    vocational, continued_education, one_party_cat, 
                    german_citizen_cat, second_citizen_cat, other_residence, EU_residence,
                    CDU_CSU, SPD, LINKE, GRUNE, AfD, FDP, OTHER_PARTY, female, CDU_2018, CSU_2018, SPD_2018, FDP_2018, GRUNE_2018, LINKE_2018, 
                    AfD_2018, NPD_REPUB_RECHTE_2018,OTHER_PARTY_2018, NOVOTE_2018, NOELEGIBILITYVOTE_2018,no_doctor_3month, HOMEOFFICE, CONTACTABROAD 
                    
  ), sum, na.rm=TRUE)

df_week_agg_mean_iso <- left_join(df_agg_iso, df_mean_iso, by=c("weekyear"="weekyear", "iso2.y"="iso2.y"))

df_long_agg_iso = df_long %>%
  group_by(iso2.y, weekyear) %>%
  slice(1) %>%
  ungroup()

df_join_w_kreis_iso <-left_join(df_week_agg_mean_iso, df_long_agg_iso[,c("iso2.y", "weekyear", 
                                                                         "country_code_kreis","oxf_closure_ger","oxf_closure_den",
                                                                         "oxf_closure_pol","oxf_closure_cze", "oxf_closure_aus",
                                                                         "oxf_closure_swi","oxf_closure_fra","oxf_closure_lux",
                                                                         "oxf_closure_bel", "oxf_closure_ned","treat1_DE_CL_OXlib",
                                                                         "treat2_DE_CL_OXstr","treat3_neigh_CL_OXlib",
                                                                         "treat4_neigh__CL_OXstr" , "treat5_any_CL_OXlib",
                                                                         "treat6_any_CL_OXstr", "treat7_OE_CL_OXlib",
                                                                         "treat8_OE_CL_OXstr","treat9_ext_KL_OXlib",
                                                                         "treat10_ext_KL_OXstr", "treat11_any_KL_OXlib",
                                                                         "treat12_any_KL_OXstr", "treat13_ext_KL_OXlib_25" ,
                                                                         "treat14_ext_KL_OXstr_25","treat15_any_KL_OXlib_25" ,
                                                                         "treat16_any_KL_OXstr_25", "treat17_OE_KL_OXlib",
                                                                         "treat18_OE_KL_OXstr","treat19_OE_KL_OXlib_25", 
                                                                         "treat20_OE_KL_OXstr_25","treat21_any_lib_corecontrol",
                                                                         "treat22_any_str_corecontrol",  "dist_25","dist_25_ind","dist_50","dist_50_ind","border_region_25",
                                                                         "border_region_50",
                                                                         "border_region_ind_25", "border_region_ind_50", "dist_extremes", "dist_extremes_ind",
                                                                         "code_m18_030")
],
by=c("iso2.y" = "iso2.y", "weekyear" = "weekyear"))

df_join_w_kreis_iso$treat1 = ifelse(df_join_w_kreis_iso$syear < 2020, 0, df_join_w_kreis_iso$treat1_DE_CL_OXlib)
df_join_w_kreis_iso$treat2 = ifelse(df_join_w_kreis_iso$syear < 2020, 0, df_join_w_kreis_iso$treat2_DE_CL_OXstr)
df_join_w_kreis_iso$treat3 = ifelse(df_join_w_kreis_iso$syear < 2020, 0, df_join_w_kreis_iso$treat3_neigh_CL_OXlib)
df_join_w_kreis_iso$treat4 = ifelse(df_join_w_kreis_iso$syear < 2020, 0, df_join_w_kreis_iso$treat4_neigh__CL_OXstr)
df_join_w_kreis_iso$treat5 = ifelse(df_join_w_kreis_iso$syear < 2020, 0, df_join_w_kreis_iso$treat5_any_CL_OXlib)
df_join_w_kreis_iso$treat6 = ifelse(df_join_w_kreis_iso$syear < 2020, 0, df_join_w_kreis_iso$treat6_any_CL_OXstr)
df_join_w_kreis_iso$treat7 = ifelse(df_join_w_kreis_iso$syear < 2020, 0, df_join_w_kreis_iso$treat7_OE_CL_OXlib)
df_join_w_kreis_iso$treat8 = ifelse(df_join_w_kreis_iso$syear < 2020, 0, df_join_w_kreis_iso$treat8_OE_CL_OXstr)
df_join_w_kreis_iso$treat9 = ifelse(df_join_w_kreis_iso$syear < 2020, 0, df_join_w_kreis_iso$treat9_ext_KL_OXlib)
df_join_w_kreis_iso$treat10 = ifelse(df_join_w_kreis_iso$syear < 2020, 0, df_join_w_kreis_iso$treat10_ext_KL_OXstr)
df_join_w_kreis_iso$treat11 = ifelse(df_join_w_kreis_iso$syear < 2020, 0, df_join_w_kreis_iso$treat11_any_KL_OXlib)
df_join_w_kreis_iso$treat12 = ifelse(df_join_w_kreis_iso$syear < 2020, 0, df_join_w_kreis_iso$treat12_any_KL_OXstr)
df_join_w_kreis_iso$treat13 = ifelse(df_join_w_kreis_iso$syear < 2020, 0, df_join_w_kreis_iso$treat13_ext_KL_OXlib_25)
df_join_w_kreis_iso$treat14 = ifelse(df_join_w_kreis_iso$syear < 2020, 0, df_join_w_kreis_iso$treat14_ext_KL_OXstr_25)
df_join_w_kreis_iso$treat15 = ifelse(df_join_w_kreis_iso$syear < 2020, 0, df_join_w_kreis_iso$treat15_any_KL_OXlib_25)
df_join_w_kreis_iso$treat16 = ifelse(df_join_w_kreis_iso$syear < 2020, 0, df_join_w_kreis_iso$treat16_any_KL_OXstr_25)
df_join_w_kreis_iso$treat17 = ifelse(df_join_w_kreis_iso$syear < 2020, 0, df_join_w_kreis_iso$treat17_OE_KL_OXlib)
df_join_w_kreis_iso$treat18 = ifelse(df_join_w_kreis_iso$syear < 2020, 0, df_join_w_kreis_iso$treat18_OE_KL_OXstr)
df_join_w_kreis_iso$treat19 = ifelse(df_join_w_kreis_iso$syear < 2020, 0, df_join_w_kreis_iso$treat19_OE_KL_OXlib_25)
df_join_w_kreis_iso$treat20 = ifelse(df_join_w_kreis_iso$syear < 2020, 0, df_join_w_kreis_iso$treat20_OE_KL_OXstr_25)
df_join_w_kreis_iso$treat21 = ifelse(df_join_w_kreis_iso$syear < 2020, 0, df_join_w_kreis_iso$treat21_any_lib_corecontrol)
df_join_w_kreis_iso$treat22 = ifelse(df_join_w_kreis_iso$syear < 2020, 0, df_join_w_kreis_iso$treat21_any_lib_corecontrol)

df_join_w_kreis_iso$code_m18_030 [df_join_w_kreis_iso$syear<=2019]=0

#Aggregate NEW BORDER DATASET by weekyear
names(NEW_border_closures)

library(dplyr)
NEW_border_mean_iso <- NEW_border_closures %>%
  group_by(newweekyear, country_code) %>%
  summarise_at(vars("internal_closure", "external_closure", "closure_both", "quarantine_until_june", "risk_areas", 
                    "quarantine_continuous"), mean, na.rm=TRUE)
names(NEW_border_mean_iso)

# merge new border closure dataset with old one
FINAL_border_df_dist <- list(df_join_w_kreis_iso, NEW_border_mean_iso) %>%
  reduce(left_join, by=c("weekyear" = "newweekyear", "iso2.y"="country_code"))

####Replace NA with 0 for the previous years within the new treatment variables
FINAL_border_df_dist$internal_closure[as.Date(FINAL_border_df_dist$weekyear, format="%Y-%W") < as.Date("2020-01", format="%Y-%W")]=0
FINAL_border_df_dist$external_closure[as.Date(FINAL_border_df_dist$weekyear, format="%Y-%W") < as.Date("2020-01", format="%Y-%W")]=0
FINAL_border_df_dist$closure_both[as.Date(FINAL_border_df_dist$weekyear, format="%Y-%W") < as.Date("2020-01", format="%Y-%W")]=0
FINAL_border_df_dist$quarantine_continuous[as.Date(FINAL_border_df_dist$weekyear, format="%Y-%W") < as.Date("2020-01", format="%Y-%W")]=0
FINAL_border_df_dist$risk_areas[as.Date(FINAL_border_df_dist$weekyear, format="%Y-%W") < as.Date("2020-01", format="%Y-%W")]=0
FINAL_border_df_dist$quarantine_until_june[as.Date(FINAL_border_df_dist$weekyear, format="%Y-%W") < as.Date("2020-01", format="%Y-%W")]=0

FINAL_border_df_dist = left_join(FINAL_border_df_dist, df_filled_small_unique_agg, by=c("iso2.y" = "iso2"))
summary(FINAL_border_df_dist$iso2_pop)

#Add infection data (already summarized by iso & week)
border_region_infection_rate <- read_csv("~/work/April_2024/R_in/border_region_infection_rate.csv")

border_region_infection_rate <- border_region_infection_rate %>%
  mutate(iso = case_when(
    country == "Austria" ~ 1,
    country == "Belgium" ~ 2,
    country == "Switzerland" ~ 3,
    country == "Czechia" ~ 4,
    country == "Denmark" ~ 6,
    country == "France" ~ 7,
    country == "Luxembourg" ~ 8,
    country == "Netherlands" ~ 9,
    country == "Poland" ~ 10
  ))

table(border_region_infection_rate$iso)

FINAL_border_df_dist <- list(FINAL_border_df_dist, border_region_infection_rate) %>%
  reduce(left_join, by=c("weekyear" = "year_week", "iso2.y"="iso"))

summary(FINAL_border_df_dist$var_rate_14_day_per_100k)
summary(border_region_infection_rate$var_rate_14_day_per_100k)

saveRDS(FINAL_border_df_dist,"~/work/November_2024/R_out/FINAL_border_df_dist25.rds")
#### the end #####